Wednesday, April 25, 2012

SQL SERVER – Step by Step SQL Server Trace (SQL Server Profiler)

SQL Server – default trace FAQ
·        Has someone deleted a table?
·        Are you trying to track auto grow events?  Problem scenarios such as Database auto grow and slow database recovery
·        When did SQL memory usage change? Read SQL Server Performance, memory pressure and memory usage for SQL Server memory analysis
·        SQL Server security changes?
The default trace has loads of information.

What is TRACE?

               A Trace allows you to track the specific and particular actions and event that performed against a SQL Server databases. A TRACE provide vital and valuable information and details for troubleshooting and monitor database issues, problems and tuning database engine performance.

What type of data is available from the default trace?

 Object creation, object deletion, error events, auditing events, full text events

What sort of events does the default trace file capture?

--returns full list of events

SELECT *  FROM sys.trace_events

--returns a full list of categories

SELECT * FROM sys.trace_categories

--returns a full list of subclass values

SELECT * FROM sys.trace_subclass_values
How many types of TRACE categories?
1.      Cursors
2.      Database
3.      Errors and Warnings
4.      Locks
5.      Objects
6.      Performance
7.      Scans
8.      Security Audit
9.      Server
10.  Sessions
11.  Stored Procedures
12.  Transactions
13.  TSQL
14.  User configurable
15.  OLEDB
16.  Broker
17.  Full text
18.  Deprecation
19.  Progress Report
20.  CLR
21.  Query Notifications

How do I check default trace is on?

SELECT * FROM sys.configurations WHERE configuration_id = 1568

How do I enable default trace?

sp_configure 'show advanced options', 1;
sp_configure 'default trace enabled', 1;

How do I find the default trace file?

SELECT * FROM ::fn_trace_getinfo(0)

How can I list objects deleted in the last 24 hrs from a specific database?

FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_154.trc',0) tf

INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id

WHERE databasename = 'AdventureWorks' AND
objectname IS NOT NULL AND
te.category_id = 5 AND
te.trace_event_id = 47 
--SQL server- 47 trace event it is for Oject Deleted.

Like and Share to SQL Integrity Blog