Click here to Skip to main content
15,867,957 members
Articles / Database Development / SQL Server
Tip/Trick

How to trace SQL Server Express without using SQL Profiler

Rate me:
Please Sign up or sign in to vote.
5.00/5 (7 votes)
20 Feb 2012CPOL 99.2K   16   4
SQL Profiler is not included with SQL Express, this tip will help if you need to track client activity.
To trace T_SQL queries submitted to SQL Express:

Start the SQL Express Service from the command prompt (run command prompt as Administrator if you are working on Windows 7 or Vista) with the following Trace Flag:


net start MSSQL$SQLEXPRESS /T4032

This now causes all queries issued by all conections to be traced. In order to have the trace output sent to the error log, enable trace flag 3605.


Execute the following in SQL Server Management Studio:


dbcc traceon(3605, -1) -- The -1 makes this global, i.e. applies to all sessions / connections

The logs can be found in C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG.


The logs will now contain the queries that were issued by the clients.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionIdealSqlTracer is a Simple, Free, Open Source Alternative to SQL Profiler Pin
Wade Harvey9-Jul-17 9:34
Wade Harvey9-Jul-17 9:34 
QuestionThe log file location varies in different machine. Pin
leiyangge11-Mar-14 20:19
leiyangge11-Mar-14 20:19 
QuestionSQL Server Profiler 2012 Free Pin
Ivan Dario Ospina13-Jun-13 7:54
Ivan Dario Ospina13-Jun-13 7:54 
BugRe: SQL Server Profiler 2012 Free Pin
Member 871442115-Apr-15 8:15
Member 871442115-Apr-15 8:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.