SQL-ASK For DBAs: How to Capture a Trace by creating template for specified time

How to Capture a Trace by creating template for specified time.

Using below query we can see the all trace file and status in the SQL Server.

Query: Select * from sys.traces

· Status: 0 means Trace is not running

· Status: 1 means Trace is running.

Now open the Profiler connect to the SQL server instance and Select below mentioned/required events (RPC Completed and SQL:BatchCompleted).

Also select all the columns which need to be captured, like SPID, host name, database name, Login name, application name, user name, CPU, Read, Writes, Durtaion, etc.

Note: Make sure the trace should capture all relevant and required details so must apply filters like Database name, Application name, Logins name etc so the trace file will not be big and easy to troubleshoot the issue.

Please refer below 5 snapshots.

Edit filter and provide database name.

Select text data (by default is not selected)

Once trace configuration is ready run the Trace by clicking on Run button and then immediately stop that trace by clicking Stop button.

Now we need to export the trace script in new query window. Please follow the steps:

Go to file à Script Trace Definition à For SQL server 2005-2014.

The scrip will be export in new query window

Important: Here we need to change three things. Please refer below 4 snapshots:

· Provide size of trace: @maxfilesize=50000 (means 50 GB)

· Provide trace file path : ‘F:\Traceoutput\sampletrace’ (provide backup drive path)

Note: Do not provide .trc extension in the path

· @TraceId - To Enable or disable the Trace: @traceId, 1(One means enable the trace, Zero means disable the trace)

You can save this Trace file in backup drive for future use.

Now Run(F5) the trace script and then check the trace created or not and check the status of it. See below:

We can see the trace id 2 is created and which running (status=1), and can see the path and other details too.

Verify the trace file in the drive.

You would see the trace is running but trace file size is showing Zero KB data. In that case you can copy the 0KB file and paste it in the same location and you would able to see the trace is capturing.

Key points:

ü You can enable, disable and delete the created Trace template.

For enable trace:

Exec sp_trace_setstatus 2,1

For Disable trace:

Exec sp_trace_setstatus 2,0

For delete template trace:

Exec sp_trace_setstatus 2,2

ü If you deleted the trace and you want to create new trace again, first delete the old file from the drive and change the file path in the script.

ü Once you running the trace for specific time then do not forget to disable the trace .

Command:

Exec sp_trace_setstatus 2,0

Situation: If you have to run the trace for any specific time then you can create two SQL agent job and schedule it for specific time.

Ø 1^st job (Start Trace) is to enable to trace.

Add following command in SQL Job step:

Exec sp_trace_setstatus 2,1

Ø 2^nd job (Stop Trace) is to disable the trace.

Add following command in SQL Job step:

Exec sp_trace_setstatus 2,0

See below:

Once the job run on scheduled time then we can open the capture trace file, which would look like below:

Now go to File and save the trace file in the table. Select the DBA_tool database and provide the table name as Trace_<currectdate>. And click ok and wait to create a table and load all trace data in the table.

Refer below snapshot:

Here we can see the trace table created.

Now verify the table data. See below

Verify the total Row count of the table. See below

Now DBA team need to Annalise the which data is unwanted and remove it from the table like any transaction which capture by SQL Agent job, EM7, NULL records, WatchDogAgent.

Please see below 4 snapshots, where we have delete such data from the Trace table.

Now Run the below select operation and we would able to fetch those transaction which transaction is taking more time (time is in Second). See below 2 snapshots-

Now you can see the most time taking query and it end time and based on the end time and SPID we would dig more in Spotlight tool. See below:

Connect to the RDS à Windows Jump Station (moc-wnjs.gmti.gbahn.net & phx-wnjs.gmti.gbahn.net) à  DBA Utility Server (moc-wn7634.gmti.gbahn.net) à

Connect to Spotlight Software  à SQL Activity à Select Playback option and provide the EndTime and check the blocking during that pirod.

Please refer below 5 snapshots for the same-

Based on the investigation blocking and the text data, we would able to know the SP name which taking more time and any blocking was happened during the transaction was running.

See below 2 snapshot of that SP, like this DBA team need to find out the top 50 SPs name (text data and SPID etc.) Also need to troubleshoot those SPs.

See below 3 snapshot.