In this post i would like to demonstrate how to create and run a trace on a SQL Server.
Let us start by understanding what a trace does for us. It helps us gather events that are happening on the instance. These events gathered, help us analyze and understand the state of the SQL server or help us understand any specific issue that we are troubleshooting. Let us understand this with an example:
Let us start by understanding what a trace does for us. It helps us gather events that are happening on the instance. These events gathered, help us analyze and understand the state of the SQL server or help us understand any specific issue that we are troubleshooting. Let us understand this with an example:
Step 1: Open SQL Server Management Studio(SSMS)
Step 2: Click on Tools -> Click on SQL Server Profiler
Step 3: Once Profiler opens it will ask for the name of the SQL server on which you want to run the trace
Step 4: In the next window we need to feed the profiler with what we want to capture such as events,template. We can also set a stop time for the trace to stop automatically. The general tab also allows to decide where we want to save the trace file i.e we can either save the trace file as a file or save the data as a table in a database. Apart from that the template drop down helps us decide what kind of data we would like to capture. In our example we will save the trace in a file. We also need to set the maximum file size and if a file would roll over in case the maximum limit is breached.
Step 5: Now let us click on next tab “Events Selection”
Step 6: Check the “Show all events” and “Show all columns” options. Scroll down to the “Stored procedures” events and check the below marked options. Then scroll down to the “TSQL” events and check the following options as below
Step 7: On the same tab we would see to buttons “Column Filters” and “Organize Columns”. Click on “Column Filters” and browse through the options it brings along. you might want to filter out any particular aspect of the system on which you want to collect data. In this example we would select the below
Step 8: Click on OK and our trace is ready to run. Click on Run and you would see the trace capturing data
Step 9 : To Stop the trace click on the stop button
If we want we can save the definition of the trace as a T-SQL script. This is how we would achieve the same.
Step 1: Click on File -> Export -> Script Definition -> For SQL Server 2005 – 2008 R2
Step 2: Save the SQL script in a desired Location
In the next post i will demonstrate how to use this SQL script for later use for capturing data. I hope this demo was useful.
Step 2: Click on Tools -> Click on SQL Server Profiler
Step 3: Once Profiler opens it will ask for the name of the SQL server on which you want to run the trace
Step 4: In the next window we need to feed the profiler with what we want to capture such as events,template. We can also set a stop time for the trace to stop automatically. The general tab also allows to decide where we want to save the trace file i.e we can either save the trace file as a file or save the data as a table in a database. Apart from that the template drop down helps us decide what kind of data we would like to capture. In our example we will save the trace in a file. We also need to set the maximum file size and if a file would roll over in case the maximum limit is breached.
Step 5: Now let us click on next tab “Events Selection”
Step 6: Check the “Show all events” and “Show all columns” options. Scroll down to the “Stored procedures” events and check the below marked options. Then scroll down to the “TSQL” events and check the following options as below
Step 7: On the same tab we would see to buttons “Column Filters” and “Organize Columns”. Click on “Column Filters” and browse through the options it brings along. you might want to filter out any particular aspect of the system on which you want to collect data. In this example we would select the below
Step 8: Click on OK and our trace is ready to run. Click on Run and you would see the trace capturing data
Step 9 : To Stop the trace click on the stop button
If we want we can save the definition of the trace as a T-SQL script. This is how we would achieve the same.Step 1: Click on File -> Export -> Script Definition -> For SQL Server 2005 – 2008 R2
Step 2: Save the SQL script in a desired Location
In the next post i will demonstrate how to use this SQL script for later use for capturing data. I hope this demo was useful.
No comments:
Post a Comment