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