Default Trace – SQL Server 2005,2008,R2
June 5, 2011
Posted by on
There is a times for DBA to find out the user activity since last one hour or more and you are not have setup any trace or audit to find out activity. You are asked to find out the users who deleted the database or what kind of queries running since last 5 hours or more. In this scenario you are not lost still you can trace out the things using default trace in SQL server.
First find out the traces running on your server using below query, querying sys.traces
Then from the result, copy the path of the trace file from path column and paste into the below query and execute it.
FROM fn_trace_gettable('"S:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_16303.trc', 1)
If you check in the physical path folder you all see multiple trace files with names like "log_16303.trc" ordered by date and time.
Please feel free to leave a comment how helpful to you........