Scheduling a SQL Server Profiler TraceWritten By: David Bird -- 3/25/2009 -- 0 commentsStay informed - get the MSSQLTips.com newsletter and win - cli......
Scheduling a SQL Server Profiler Trace
Written By: David Bird -- 3/25/2009 -- 0 comments     
|
Stay informed - get the MSSQLTips.com newsletter and win - click here
|
Problem
You want to schedule a trace, but SQL Profiler does not have a built-in scheduling option. So how do you use SQL Agent to schedule a SQL Profiler trace?
Solution
SQL Profiler traces are built using system stored procedures. You can use the SQL commands from an existing profiler trace and construct your own stored procedure that creates and starts a SQL Profiler trace. You will need to specify some additional settings using your stored procedure. These include run duration, file size, and trace output file location. Instructions on how to create, execute, and stop the profiler trace using this stored procedure are covered in detail.
Create the Trace Definition
The most efficient means to define the SQL commands used for constructing a profiler trace is to use SQL Profiler.
- Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace.
- Start the trace and then stop it.
- Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005
Note: For SQL Sever 2000 and 2008 choose the appropriate output type.
- Save the trace file.
Create a Profile Trace Stored Procedure
1/6 1 2 3 4 5 6 下一页 尾页 |
