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 columnsdo not appear in the Selected databox, click to select the item in the Availabledata 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
