Default Trace – SQL Server 2005,2008,R2

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

FROM 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.

SELECT SPID,LoginName,StartTime,reads,writes,servername,
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........

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s