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.
Ø
1st job (Start Trace) is to enable to
trace.
Add following command in SQL Job
step:
Exec sp_trace_setstatus 2,1
Ø
2nd 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.
Save top 50 records in text file with CSV format and send
mail to Lineup team also update the template SQL Script and output file in the
Tickets.
Also DBA team need to provide you suggestion on the top performance
impacting queries/SP.
Send mail to LineUp team with below format with
investigation details.
