 |
|
|
Home > How to use SQL Trace definition |
|
|
|
|
|
|
How to use SQL Trace definition |
|
| 19 Mar 2007 | By Michelle Gutzait |
|
|
How to use the trace definition:
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 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.
Since we would like to have reports in the "Monitor" environment, we probably need the Database Name from production, along with the Database ID that we import in the trace results. This is why the DTS/SSIS should also import the list of databases and database IDs from production.
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
Step-by-Step Guide: How to interpret performance metrics in SQL Server 2005
Using SQL Server Profiler with Analysis Services
FAQ: DTS packages in SQL Server
|
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|
Home: 
