Thursday, 8 August 2013

Tracing SQL Server Express without using SQL Profiler

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.


To install sql profiler with an express edition-
Here are the steps for these installations:
Developer or Standard Edition of SQL Server 2008 R2:
In installation directory, run the following from an admin command prompt:
setup.exe /FEATURES=Tools /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=MSSQLSERVER /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /IACCEPTSQLSERVERLICENSETERMS
- This will install only the tools for SQL Server, including SQL Profiler.
SQL Server 2008 R2 Express Edition (database engine only):
- Execute SQLEXPR32_x86_ENU.exe, then after the main screen is shown, copy the contents of the temporary directory to an installation directory. Run the following from an admin command prompt:
setup.exe /FEATURES=SQLEngine /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=SQLEXPRESS /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /SQLSYSADMINACCOUNTS=”BUILTIN\Users” /IACCEPTSQLSERVERLICENSETERMS
- This will install the database engine and add BUILTIN\Users to the sysadmin role

No comments:

Post a Comment