Home > 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