Capture all statements for a SQL Server session

By:   |   Updated: 2009-03-18   |   Comments (4)   |   Related: More > Scripts

Problem

One thing that frustrates me is that sometimes I write these great queries, but often forget to save them or can't remember exactly what the query looked like that I ran five iterations ago. One smart thing to do would be to always save your scripts, but when your in the process of trying a bunch of different things there is always the chance you may loose something. In this tip we look at creating a server side trace for one session and capturing all completed batches that are run, so you can find that great query that you thought you may have lost.

Solution

The first thing that needs to be done is to create two stored procedures, which I created in my master database. These allow you to create a server side trace and also turn off a server side trace. For more information about a server side trace refer to this tip, SQL Server Performance Statistics Using a Server Side Trace.

Starting the Trace

The first stored procedure shown below starts the server side trace, but it also excepts the SPID for the particular session you want to capture. It will create a file name such as "TraceMySession_52_d20090317120912.trc" to keep the trace session unique. This is set to save to the file to the "C:" drive of the server, so you may want to change this to a different directory. The script is also setup to create a 5MB file and then roll over to additional files. Again you can check out the tip mentioned above for more about these settings.

The other thing that we are doing is only capturing the SQL:BatchCompleted events. This way the trace file doesn't get very large. The three data columns that will be captured are TextData, StartTime and SPID.

To create the procedure, copy the code below and execute it. Like I said I created this in the master database, but this could be created in a different database.

CREATE PROCEDURE [dbo].[spTraceMySessionStart] @spid INT 
AS 

-- Create a Queue 
DECLARE @rc INT 
DECLARE @TraceID INT 
DECLARE @maxfilesize bigint 
SET @maxfilesize = 5 
DECLARE @filename NVARCHAR(245) 
SET @filename = 'C:\TraceMySession_' 
   + CONVERT(NVARCHAR(10),@spid) 
   + '_d' 
   + REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') 
   + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','') 

EXEC @rc = sp_trace_create @TraceID output, 2, @filename, @maxfilesize, NULL 
IF (@rc != 0) GOTO error 

-- Set the events 
DECLARE @on bit 
SET @on = 1 
EXEC sp_trace_setevent @TraceID, 12, 1, @on 
EXEC sp_trace_setevent @TraceID, 12, 12, @on 
EXEC sp_trace_setevent @TraceID, 12, 14, @on 

-- Set the Filters 
DECLARE @intfilter INT 
DECLARE @bigintfilter bigint 
EXEC sp_trace_setfilter @TraceID, 12, 1, 0, @spid 

-- Set the trace status to start 
EXEC sp_trace_setstatus @TraceID, 1 

-- display trace id for future references 
SELECT [email protected] 
GOTO finish 

error: 
SELECT [email protected] 

finish: 

Stopping the Trace

This stored procedure below will stop and close the trace once you are finished. If this is not run the server side trace will continue to run and collect data for any other session that happens to have the same SPID.

This stored procedure excepts one parameter which is TraceID. When you actually run the first stored procedure it will give you the TraceID that was created. This is the value that you pass to this second stored procedure to stop and close the trace.

I also created this in my master database, but this can be created in a different database as well.

CREATE PROCEDURE [dbo].[spTraceMySessionStop] @traceId INT 
AS 
EXEC sp_trace_setstatus @traceId,0 
EXEC sp_trace_setstatus @traceId,2 

Let's Try It

So let's say we have the following code that we want to run.

EXEC master.dbo.spTraceMySessionStart 52 

USE AdventureWorks 
GO 

SELECT name 
FROM sys.sysobjects WHERE xtype = 'U' 
GO 
SELECT TOP 10 Title 
FROM HumanResources.Employee 
GO 

EXEC master.dbo.spTraceMySessionStop 2 

The first thing that we need to do is start the trace which is the first line of my script. I can see my SPID at the bottom of the screen which is 52, which has been highlighted below.

When I run the store procedure, the output shows me the TraceID which is "2". We need to keep this value for later.

After I have run my other commands and I am done, I can run the last line to stop and close the trace and use the TraceID value of "2" which we got above.

Reviewing What Was Captured

If I open the file that was created using Profiler I can see all of the commands that were run for my session. You could then copy these lines into a query window for reuse.

Another option is to load the file that was created to a SQL Server table or just query the data using SQL Server. The steps for doing this can be found in this tip.


SELECT TEXTData, StartTime 
FROM ::fn_trace_gettable('c:\TraceMySession_52_d20090317120542.trc', DEFAULT) 
WHERE TEXTData IS NOT NULL 
Next Steps
  • Hopefully this gives you another tool you can use to capture all of the batches that are run for a session for future reference.
  • Try changing this to add other columns or events
  • Also change this to capture multiple SPIDs at once
  • Refer to this tip for more information on creating a Server Side Trace


Last Updated: 2009-03-18


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, December 08, 2011 - 9:11:45 AM - Paul Sligar Back To Top

Another way to accomplish this is to install the <FREE> SSMS Tools Pack add-in for SSMS. It will automatically save all your run queries and allow you to search for them. This is only one of many of the benefits in this tool. Thanks to MVP Mladen Prajdic for providing this tool to the community.

 


Friday, October 21, 2011 - 9:44:21 AM - Greg Robidoux Back To Top

Thanks Usman for pointing that out.  That is a good point if rollover files are to be created.   I think for this type of capture the intent was for short data collections, but I can see that this would be an issue for a trace that is left running for awhile.

 


Friday, October 21, 2011 - 9:16:19 AM - Usman Butt Back To Top

Hi,

Thanks for a nice tip. But I must say that according to BOL underscores(_) are not recommended in trace file names.

QUOTE

If you use the TRACE_FILE_ROLLOVER option, we recommend that you do not use underscore characters in the original trace file name. If you do use underscores, the following behavior occurs:

  • SQL Server Profiler does not automatically load or prompt you to load the rollover files (if either of these file rollover options are configured).

  • The fn_trace_gettable function does not load rollover files (when specified by using the number_files argument) where the original file name ends with an underscore and a numeric value. (This does not apply to the underscore and number that are automatically appended when a file rolls over.)

UNQUOTE

 

Best Regards,
Usman Butt


Thursday, February 25, 2010 - 10:35:51 AM - nosnetrom Back To Top
Excellent tip! It could really save one's bacon! :D


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools