Now that you have the trace definition on hand, you can run a job that creates a trace and saves the results to a table every X minutes.
Here are the steps to accomplish that:
- Create a table that holds your trace number.
- Create a table that will hold the trace results.
- Create a job that runs every X minutes (e.g., 10 minutes). The job will do the following:
a. Check if there is a trace running (querying the table that holds the trace number)
b. If a trace is running:
→ Stop the trace.
→ Insert the results from the file into the table holding the trace results.
→ Start the trace.
Else (the trace is not running):
→ Create and start the trace.
→ Keep the trace number in the table.
You probably want to trace the production environment, but remember to keep the trace results in a separate environment (let's call it "Monitor" environment). This way production performance is not affected.
In this case, you would need to use a DTS/SSIS package that does the following:
- In production -- Create a table that holds your trace number.
- In "Monitor" environment -- Create a table that will hold the trace results.
- Create a job that runs every X minutes (e.g., 10 minutes). The job will run a DTS/SSIS package that does the following:
c. In production --
i. Checks if there is a trace running (querying
Requires Free Membership to View
- the table that holds the trace number)
ii. If a trace is running:
→ Stops the trace.
→ In "Monitor" environment -- inserts the results from the file into the table holding the trace results. Makes sure the directory where the trace file is located has a share name and can be accessed by the process that reads it in the "Monitor" environment.
→ Start the trace.
Else (the trace is not running):
→ Create and start the trace.
→ Keep the trace number in the table.
If we are constantly running reports in the "Monitor" environment, we would need to import the database list to a separate table and update the real database's table after the import.
Furthermore, we probably want to purge historical data so the "Monitor" database will not grow infinitely. It is recommended to add a step in the package to purge historical data from the "Monitor" database.
| ABOUT THE AUTHOR: |
|
Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more. Copyright 2007 TechTarget |
More on SearchSQLServer.com
This was first published in March 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation