Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 14, 2003

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

By Andrew Novick

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM