Monday 17 April 2017

What is TRACE and How to configure, Use and Read Trace with Examples in SQL Server?

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.

Why we use SQL Trace?
·  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 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;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

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?

SELECT *
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.



How to Create a Trace (SQL Server Profiler)

This topic describes how to use SQL Server Profiler to create a trace.

1.      Profiler -> File menu, clicks New Trace, and connects to an instance of SQL Server.




2.      In the Trace name box, type a name for the trace.
3.      In the Use the template list, select a trace template on which to base the trace, or select Blank if you do not want to use a template.
4.      To save the trace results, do one of the following:

o        Click Save to file to capture the trace to a file. Specify a value for Set maximum file size. The default value is 5 megabytes (MB).
Optionally, select Enable file rollover to automatically create new files when the maximum file size is reached. You can also optionally select Server processes trace data, which causes the service that is running the trace to process trace data instead of the client application. When the server processes trace data, no events are skipped even under stress conditions, but server performance may be affected.
o        Click Save to table to capture the trace to a database table.
Optionally, click Set maximum rows, and specify a value.

When you do not save the trace results to a file or table, you can view the trace while SQL Server Profiler is open. However, you lose the trace results after you stop the trace and close SQL Server Profiler. To avoid losing the trace results in this way, click Save on the File menu to save the results before you close SQL Server Profiler.
5.      Optionally, select the Enable trace stop time check box, and specify a stop date and time.
6.      To add or remove events, data columns or filters, click the Events Selection tab.

7.      Click Run to start the trace.


Please provide your input if anything missing here- Jainendra Verma

No comments:

Post a Comment