Tracing sql to a process

Question

Tracing sql to a process

Joseph Milora

Valued Member

Points: 55
More actions
January 11, 2008 at 9:21 am

#71054

Hi all,
We've been experiencing application lock ups lately at a rate of once or twice a day. The application hangs, which I can correct quickly by killing the process on the database that is holding the locks. I know there are some architecture issues that need to be fixed on the application side, but right now I just want to identify the sql being executed by the blocking process. I've tried looking at syscacheobjects but I don't know how to resolve the bucketid to user process id.
Is there a way, short of using the profiler tool, to identify the sql being executed by a user process in sql server 2000?
Thanks.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Before killing the process that is holding the locks you can run DBCC InputBuffer([spid]) which will give you the last sql statement executed by the given spid.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Joseph Milora Valued Member Points: 55 More actions · Answer 2

Unfortunately, I get back a begin trans or some other statement, but never the actual sql.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Profiler is probably going to be the easiest and fastest way to find the process causing the problem. Are you using sp_who or sp_who2 to find the blocking process? sp_who2 gives a little more information and may help by identifying the application

Are you using stored procedures for data access or does the application have sql code?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Joseph Milora Valued Member Points: 55 More actions · Answer 4

I was afraid of that. I was hoping there was a way to tie the bucketid from the syscacheobjects or use another system table so I wouldn't have to worry about the performance impact of the profiler running until the next lock up.

The application uses sql code and stored procedures.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Try this code:

declare @handle1 binary(20)

declare @handle2 binary(20)

--select * from sysprocesses

declare @handle1 binary(20)

declare @handle2 binary(20)

selecttop 1

@handle1 = a.sql_handle,

@handle2 = b.sql_handle

from

master..sysprocesses a

join

master..sysprocesses b

on a.blocked = b.spid and a.spid <> b.spid

where

a.blocked <> 0

if @handle1 is not null or @handle2 is not null

begin

select @handle1, @handle2

select * from ::fn_get_sql(@handle1)

select * from ::fn_get_sql(@handle2)

end

Joseph Milora Valued Member Points: 55 More actions · Answer 6

Thanks!

Is the get sql function available on all versions of sql server 2000, or after a particular service pack?

Joe

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 7

The real culprit is likely going to turn out to be a deadlock. You need to read "Deadlocks, detecting and ending" in Books Online. Pay particular attention to "Trace Flag 1204".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joseph Milora Valued Member Points: 55 More actions · Answer 8

In this case it's actually a contention issue on the application side and there aren't any deadlocks in the database. I'm attempting to find the actual sql to determine whether it is a particular part of the application not cleaning things up properly or something in the platform code on which the application is built on.