⚠️ This page has been archived

✅ New page with updated info: ssw.com.au

Home > Archive > SSW Standards > DeveloperSQLServer > SSW Creating a SQL Server Profiler Trace

Creating a SQL Server Profiler Trace

Summary

This standard shows how to create a SQL Server Profiler Trace so as to log object changes in a Database.

The trace must track any DDL (Database Definition Language) SQL statements for a particular Database. This will not work with stored procedures, as they are compiled, and their text is not shown on the trace. As such, we can only track the creation and changes to stored procedures, not whether they modify existing objects.

NB: This trace covers both batches and individual statements. However, this may cause duplicates when there is a one-line operation to change the data. Consequently, the duplicates in the data would have to be removed before using for a process (eg to update two separate databases to the same structure)

Running Profiler:
Run sqltrace.exe from a Run Dialog box to start.
Start a new Trace:
First, Create a New trace from the File menu.

GeneralTab

a. For the trace name, use zsObjectChangeLog

b. Enter the Server Name
c. Check on "Capture to Table, and Enter for the destination table properties:
1. The Server Name on which the database resides
2. Select the database for which the trace is being done (log file kept in same database as the one being logged.)
3. Set the owner as db_owner
4. Leave the Table as zsObjectChangeLog

Events Tab


Select the TSQL event class, and add the
1. SQL:StmtComplete
2. SQL:BatchComplete
Events to the SQL Server Profiler Trace.

Data Columns Tab


Add the following columns to "Selected Data"
1. DatabaseID
2. Event Class
3. Text
4. Server Name
5. Host Name
6. Application Name
7. NT User Name
8. SQL User Name
9. StartTime
10. Duration

Filters Tab



a. Under Applications Criteria, enter: SQL Server Profiler%;%SQLAgent - Alert%
b. To focus the select to a particular database (as we wish to do), we need to determine the DatabaseID as per sysdatabases and enter it into profiler. To do this:
1. Go to SQL Server Query Analyzer (Type isqlw.exe from the Run Prompt.).
2. Type SELECT DB_ID('YourDataBaseName') and Press F5 to run.
3. Use the value in the result window (in this case, 16), and enter it into the DatabaseID Value Criteria.
4. The trace should also be restricted to DDL statements. To do this, we must filter to only include CREATE, ALTER and DROP T-SQL statements. Under the text criteria, Enter CREATE%;ALTER%;DROP%

c. Click OK, then Let the Trace Run!
End of Document