Capture all statements of an SQL Server session

Question: one thing that makes me particularly frustrated is that I often write complex queries, however, I often forget to save them or do not remember what the query was like before I ran five iterations. One wise way is to keep your scripts, but when you are trying to do a bunch of different things, you may forget something. In this article, we will look at how to create a server trace for a session and capture all running scripts in full, so that you can find the complex query that you think may be lost.

First, you need to create two stored procedures. In this technique, I create them on my primary database. They allow you to create a server-side trail, or you can turn off a server-side trail. For more information about server-side tracking, refer to the previous tip "Use a server-side tracking for SQL
Server Performance Statistics ".

Start tracing

The first stored procedure is shown as follows. It starts the server-side tracing, but also removes the spid of the specific session you want to capture. It will create a file name, such as "tracemysession_52_d20090317120912.trc", so that the trail session is unique. By default, it is stored in the server's "C:" disk, so you can change it to another directory. This script is also created to create a 5 MB file and then roll it back to another file. You can test the techniques mentioned above again to get more information about these settings.

Another thing we are doing is capturing only SQL: batchcompleted events. In this way, the trace file will not become very large. The three data fields captured are textdata, starttime, and spid.

To create this stored procedure, copy the followingCodeAnd execute it. As I said, I create thisProgramBut you can also create it in different databases.

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 traceid = @ traceid Goto finish Error: Select errorcode = @ RC Finish: Stop a trail Once you complete the tracking, the following stored procedure will stop and turn it off. If this fails, the server-side tracing will continue to run and collect any other session data that exactly has the same spid. This stored procedure excluded a parameter, that is, traceid. When you run the first stored procedure, it will give you the traceid you created. This is the value that you pass to the second stored procedure so that the trail is stuck and disabled. I also created it in my primary database, but you can also create it in any different database. Create procedure [DBO]. [sptracemysessionstop] @ traceid int As Exec sp_trace_setstatus @ traceid, 0 Exec sp_trace_setstatus @ Traceid, 2 Let's try it. Therefore, we can assume that we want to run the following code. 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 First, we need to start this trail, that is, the first line in my script. I can see my spid at the bottom, that is, 52, which has been highlighted in the following. Figure 1 When I run this stored procedure, the output result shows traceid, that is, "2 ". We need to save this value for later use. Figure 2 After I run other commands and all of them are complete, I can run the following command to stop and turn off this trace and use the traceid value "2" we obtained above ". Figure 3 View the captured If I use the probe to open the file we created, I can see all the commands run in my sessions. You can copy these commands to a query window to reuse them. Figure 4 Another option is to download a file created to the SQL Server table or use SQL Server to query only the data. Figure 5 Select textdata, starttime From: fn_trace_gettable ('C: \ tracemysession_52_d20090317120542.trc ', Default) Where textdata is not null