How to create a SQL Trace with Profiler on Microsoft SQL Server 2000-2014

TechKnowledge Content
The information in this document applies to:

- Great Plains on Microsoft SQL Server

SUMMARY

This article describes how to create a SQL trace with Profiler on Microsoft SQL Server 2000-2014.

MORE INFORMATION

Follow these steps to create a SQL Trace.

1. Click Start, point to Programs, click Microsoft SQL Server XXXX, and then click SQL Server XXXX Profiler. (Where XXXX is the version you are using.)

2. On the File menu, click New, and then click Trace.  (or File | New Trace)

3. Connect to SQL Server.

4. Click the General tab, type a name in the Trace name field, click to select the Save to file option and name the file to capture data to, and click OPEN and click Save. Back in the General tab, enter a value in the Set maximum file size (MB) box, click to select the Enable file rollover option, and then click to select the Server processes trace data option.

5. Click the Events Selection tab, mark the checkbox in the lower right for Show all events and Show all Columns.

6. Then expand the following Event classes and mark the Events defined below: 

Cursors
- CursorClose
- CursorExecute
- CursorOpen
- CursorPrepare

Errors and Warnings
- Attention
- Exception
- Missing Column Statistics

Locks
- Lock: Deadlock
- Lock: Deadlock Chain 

Sessions
- ExistingConnection

Performance
- ShowPlan All

Stored Procedures
- RPC: Starting
- RPC: Completed
- SP: Completed
- SP: Recompile
- SP: Starting
- SP: StmtCompleted
- SP: StmtStarting

TSQL
- SQL:BatchCompleted
- SQL:BatchStarting
- SQL:StmtCompleted 
- SQL:StmtStarting

If any of the event classes or event types do not appear in the Selected event classes box, click to select the item in the Available event classes box, and then click Add.

7. Click the Data Column tab, and make sure following columns appear in the Selected data box:

- Start Time
- End Time
- SPID
- Event Class
- Text Data
- Integer Data
- Binary Data
- Duration
- CPU
- Reads
- Writes
- Application Name
- NT User Name
- LoginName

If any of the columns do not appear in the Selected data box, click to select the item in the Available data box, and then click Add.

You may need to restrict the LoginName to a specific user if you are on a terminal services environment where numerous users are in the system.  Generally though we recommend not to restrict to the user, and let us know what user it is, (as it is often helpful to know what else is running at the same time.)

8. Click Run to start the trace.

9. On the File menu, click Stop Trace to stop the trace. (Or the red square icon at the top of the trace window.) Otherwise the trace will continue to run and fill the full table/file size limit defined in step 4.

10. On the File menu, click Save As, and then click Trace File. Name the file and save it. 

This article was TechKnowledge Document ID: 27316
Properties

Article ID: 857246 - Last Review: 04/05/2016 16:23:00 - Revision: 8.0

Microsoft Dynamics GP 2015, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2010, Microsoft Dynamics GP 10.0, Microsoft Dynamics GP 9.0, Microsoft Business Solutions–Great Plains 8.0

  • kbnosurvey kbhowto kbexpertiseadvanced kbmbsmigrate KB857246
Feedback