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)
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'
-- Stop Batch A-2 here
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
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
-- 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)
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 B-4
PRINT 'Batch B-4 SELECT a blocked resource.'
select * from authors
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.