Advertisements

SQL-BOX.COM

–SQL Server Storage

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

SELECT
 status, 
 path, 
 max_size, 
 buffer_count, 
 buffer_size, 
 event_count, 
 dropped_event_count 
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,
sessionloginname,TextData,ClientProcessID,ApplicationName
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........
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: