Find Out What They're Doing with fn_get_sql - Page 3

A truly real world example of using fn_get_sql might involve creating a deadlock, using sp_lock to find out which processes are blocked, and then using fn_get_sql to retrieve the text of the SQL that the blocked and blocking processes were executing. The thought of publishing code that deliberately created a deadlock some how struck me as overly risky so I've decided to use a slightly simpler example, a case of simple blocking due to a long running transaction.

The example below uses two Query Analyzer windows to run Script A and Script B. Both should be run in the Pubs sample database. The scripts include six batches. The batches should be run in numeric order. You can get the scripts from the links if you want to run them yourself. In the text that follows, I execute each batch in order and show you the results.

Start with Script A Batch A-1. It turns on trace 2861 and moves the connection into the Pubs database.

-- Batch A-1   Moves to the Pubs sample database
PRINT 'Batch A-1  Script A''s SPID = ' + CAST (@@SPID as varchar)
DBCC TRACEON(2861)
Use Pubs
go 
(Results)
Batch A-1  Script A's SPID = 53
DBCC execution completed. If DBCC printed error messages, contact your system administrator.	

Next run Script A Batch A-2. This batch begins a transaction and deletes a row in the Authors table. I have deliberately chosen an author that has not written any books so there are no referential integrity issues. Don't worry about loosing the row. We will rollback the transaction in Batch A-6. Here is Batch A-2:

-- Batch A-2
PRINT 'Batch A-2 Begin a transaction and create the blockage'
BEGIN TRAN -- the transaction will cause 
    DELETE FROM authors WHERE au_id = '527-72-3246'
GO
-- Stop Batch A-2 here
(Results)
Batch A-2 Begin a transaction and create the blockage

(1 row(s) affected)

Batch A-2 leaves open a transaction, which we will not close until Batch A-6. In Script A‑5, we'll see that the open transaction causes the SPID to hold several locks, including an exclusive lock on the row being deleted.

The next step is to open a new Query Analyzer connection using the menu item File/Connect and load Script B. The first batch in script B is B-3, which prints the SPID of the connection for Script B. We will use that SPID in batch A-5. Here is Batch B-3 with the results of running it on my system:

-- Batch B-3   Moves to the Pubs sample database
--             And prints the SPID
PRINT 'Batch B-3 Printing the SPID and Using Pubs'
PRINT 'Script B -- Has SPID ' + CAST(@@SPID as varchar)
Use Pubs
GO
(Results)
Batch B-3 Printing the SPID and Using Pubs
Script B -- Has SPID 55

You will probably get a different number for the SPID. Once again, take note of the SPID because it is needed later in Batch A-6.

Batch B-4 selects from the Authors table. Here is the batch:

-- Batch B-4 
PRINT 'Batch B-4 SELECT a blocked resource.'
select * from authors
GO

There are no results, because the batch cannot run due to the open transaction left by Batch A-2. Figure 2 shows what my Query Analyzer window looks like after I execute B-4.

Figure 2 Script B Batch B-4 is Blocked and Show It's Results

I have circled the red execution flag in purple to highlight the fact that the batch is running. If you look down in the information bar near the bottom of the figure, you will see that it had been running for one minute and 11 seconds by the time that I took the screen shot.