Audit Trace Procedure
_____________________
1. Enable C2 Auditing from DB Options.
2. Restart SQL Server.
3. Check Location of Audit Logs in MSSQL folder in C:\
4. Create AuditDB database with log and datafile in location D:\
5. Create temp_trc table in AUDITDB database using below command :

use auditdb
SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO temp_trc FROM fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\audittrace000001.trc’,default) where DatabaseName=’APPDB’and ApplicationName NOT LIKE ‘%%Repl-LogReader%%’ and TextData NOT LIKE ‘%%select%%’ and TextData NOT LIKE ‘%%FETCH%%’ and textdata NOT LIKE ‘%%merge%%’ and ApplicationName NOT LIKE ‘%%HostReplication%%’;
go
6. Create a Table audit_trc using below command.

use auditdb
select * into audit_trc from temp_trc;
go

7. Create folder “d:\tracelog”, Copy the files audit_trace.bat and audit_trace.sql to the server.
8. Create Task schedule for running audit traces every 8 hrs daily.
———-|————-|————-|————–|—————|———–

Audit Log Retention Procedure
_____________________________
1. Copy file Clean_Audit_Trace.sql and Clean_Audit_Trace.bat to “c:\tracelog” on Server.
daily (the script delete’s rows older than 1 days).

2. Schedule Clean_Audit_Trace.bat to run as batch job daily at 12:00 AM