How to use SQL Profiler to create a SQL trace in Microsoft SQL Server

View products that this article applies to.

INTRODUCTION

This article describes how to use SQL Profiler to create an SQL trace in Microsoft SQL Server 2005 or in Microsoft SQL Server 2008. (Note, you can use the same steps or selection of event classes for any version of SQL Profiler.)

Use either method below to gather the SQL Profiler Trace:

↑ Back to the top

METHOD 1 - Use a SQL Template

The templates are pre-mapped for the fields noted in Method 2, so will save you some time from having to manually map the fields. To use a SQL Trace template, follow these steps:

1. Determine what version of SQL Server you have and double-click the link below to download the zip file of SQL templates. In the zip file, click on the TRACETMPL folder and double-click on the .tdf file for your version and SQL Profiler will automatically open. (Take note of the file name as this is the name for the template you will look for in SQL profiler.) Click Yes if prompted to overwrite. Then you should get a message that the tdf file was imported successfully. Click OK. (Or you can do a Save|Save As and go to that location on double-click on the tdf file.)

SQL 2005

SQL templates for versions 2008, 2008 R2, 2012, 2014, 2016, 2017, 2019 (zip file)

2. Within SQL Profiler, click on File | New Trace. (Connect or log in as prompted.)

a. In the Trace Properties window, enter a name for Trace Name as desired.

b. Click the drop-down icon next to the USE THE TEMPLATE field, and scroll down to the end of the list and select the SQL trace template you imported in. (Should beging with 'MSGP' prefix.)

c. Click to mark the SAVE TO FILE checkbox and browse to a location you would like to save the trace file to. Save.

d. Also uncheck Enable File Rollover option (Under SAVE TO FILE section).

e. In the Events Selection tab, mark the SHOW ALL EVENTS checkbox and SHOW ALL COLUMNS in the lower right corner.

Note: All the fields in Method 2 will automatically be selected in the Events Selection tab.

3. Click RUN to start the trace and the SQL Profiler window will open and the trace is now running. Use the icons listed below to help you capture the trace. Be sure to keep the trace file as small as possible and only capture the steps in Dynamics GP to reproduce the problem. Traces too large are often not efficient or helpful.

- use the Red Square icon at the top to stop the trace.

- Use the Green Arrow icon at the top to restart the trace. (This is enabled if the trace is stopped.)

- Use the White Eraser icon (looks like a piece of chalk) to clear out the trace at any time needed.

Note: The trace file should be created at the location selected above. Send this trace file to the Microsoft Engineer, along with the information of what version of SQL you are using, and the user ID you used when performing the steps in GP.

↑ Back to the top

METHOD 2 - Manual steps

To create a SQL trace, follow these manual steps:

1. Click Start, point to Programs, click Microsoft SQL Server 20xx (your version), click Performance Tools, and then click SQL Server Profiler.

2. On the File menu, click New Trace… to open the ‘Connect to Server’ dialog box.

3. In the Server name box, type the name of the SQL Server.

4. In the Authentication list, click SQL Authentication.

5. Type the system administrator user name and the password, and then click Connect to open the Trace Properties dialog box.

6. Key a trace name. Click the Events Selection tab, and then click to select the Show all events check box in the lower-right corner.

7. Select the server event classes to the trace:

a. Expand Cursors, and then click to select the following check boxes:

•	CursorClose
•	CursorExecute
•	CursorOpen
•	CursorPrepare

b. Expand Errors and Warnings, and then click to select the following check boxes:

•	Attention
•	Exception
•	Missing Column Statistics
•	User Error Messages

c. Expand Locks, and then click to select the following check boxes

Lock: Deadlock
Lock: Deadlock Chain

d. Expand Performance, and then click to select the following check boxes:

•	ShowPlan All

e. Expand Sessions

ExistingConnection (should already be selected by default)

f. Expand Stored Procedures and click to select the following checkboxes:

g. Expand TSQL, and then click to select the following check boxes

•	RPC: Starting
•	RPC:Completed
•	SP: Completed
•	SP: Recompile
•	SP: Starting
•	SP: StmtCompleted
•	SP: StmtStarting

Note By default, the RPC: Completed check box should be selected. Do not click to clear the RPC: Completed check box.

----------------------------

•	SQL:BatchCompleted
•	SQL:BatchStarting
•	SQL:StmtCompleted
•	SQL:StmtStarting

Note By default, the SQL:BatchCompleted check box should be selected. Do not clear the SQL:BatchCompleted check box.

8. Click Organize Columns, and then make sure that the following columns appear in the Organize Columns dialog box:

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

9. Do not filter by user. We can do that on our end, as it is often helpful to see what else is running a the same time. But make sure to indicate in your email which user you where using when you return the trace file to Microsoft.

10. Click Run to run the trace and to capture SQL Server processes. (Or click the Green Arrow icon at the top of the trace.)

11. On the File menu, click Stop Trace to stop the trace. (Or click the Red Square icon at the top of the trace.

12. On the File menu, click Save As, and then click Trace File.

13. Name the file, and then save the file.

↑ Back to the top

Applies to:

↑ Back to the top

Keywords: gp, kb, kbhowto, kbmbsmigrate, kbmbspartner, kbnosurvey

↑ Back to the top

Article Info

Article ID	:	912281
Revision	:	19
Created on	:	5/28/2020
Published on	:	5/28/2020
Exists online	:	False
Views	:	502

Microsoft KB Archive Search

How to use SQL Profiler to create a SQL trace in Microsoft SQL Server

INTRODUCTION

METHOD 1 - Use a SQL Template

METHOD 2 - Manual steps

Applies to: