How to create a trace from sql server profiler
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.
---------------------------------------------------------------------------------------------------------------------------------------------
How to read the content of one or more trace files
To read the content of one or ore trace files we need to use the following system provided function by SQL Server:
fn_trace_gettable ( ‘filename’ , number_files )
Where
‘filename': is the initial trace file to be read. Filename is nvarchar(256), with no default.
Where
‘filename': is the initial trace file to be read. Filename is nvarchar(256), with no default.
number_files:Indicates the number of rollover files to be read. This number includes the initial file specified in filename.The value is an integer.
Let us see how it works with the help of an example:
Step 1: let us first find the traces running in the system
SELECT * FROM sys.traces
Go
Output gave us:
Step 2: Choose the trace that you want to open. In this case we have only one trace running. Copy the value from the path column and pass it on as a parameter in the below function
SELECT * FROM sys.fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0)WHERE TextData IS NOT NULL
Output gave us:
We can save the output in a temporary table if we want look further into the data:
Use Databasename;
GO
SELECT * INTO Temp_table
FROM fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0);
GO
Step 1: let us first find the traces running in the system
SELECT * FROM sys.traces
Go
Output gave us:
Step 2: Choose the trace that you want to open. In this case we have only one trace running. Copy the value from the path column and pass it on as a parameter in the below functionSELECT * FROM sys.fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0)WHERE TextData IS NOT NULL
Output gave us:
We can save the output in a temporary table if we want look further into the data:Use Databasename;
GO
SELECT * INTO Temp_table
FROM fn_trace_gettable(‘E:\MSSQL10.ORCH_INST_A\MSSQL\Log\log_13083.trc’,0);
GO
---------------------------------------------------------------------------------------------------------------------------------------------------
How to find if there is a trace running in the server
What is Server-side tracing?
Server-side tracing is the process of having your SQL Server machine save events to a physical file on that machine without using the Profiler client tool. Server-side tracing is enabled and controlled by using SQL Server system-supplied stored procedures and functions. With these system-supplied processes, you can identify what to trace, when to start and stop tracing, what traces are running, and view trace information stored in the trace file.
Here is how you view the number of traces currently in the system and the details of the trace:
Number of traces(active and stopped) currently in the system:
select * from sys.traces
select * from sys.traces
Here is how you view the active(trace is running) traces in the system:
SELECT * FROM :: fn_trace_getinfo(default) WHERE property = 5 and value = 1
You can stop and terminate a trace by executing the following:
EXEC sp_trace_setstatus @traceid , @status = 0 — Stops the trace
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.
EXEC sp_trace_setstatus @traceid , @status = 2 — Eliminates the definition of the trace from the server.