Thursday, November 30, 2006 1:43 PM antxxxx

script to find out what a spid is doing

If a server is behaving badly, or there is a long running process and you want to find out what it is actually doing, you might find these scripts useful. They can be used to show the query or stored procedure that is executing and the text of the statement that is executing within that procedure.

This gives a more complete picture of what is happening than by just using dbcc inputbuffer which  just displays the last statement sent by the client to sql server.

For example say have a stored procedure, uspProcedure1, which calls another procedure, uspProcedure2, inside it, and uspProcedure2 takes a long time to complete. If a client executes uspProcedure1 and you run dbcc inputbuffer on that spid, it will just show uspProcedure1 executing, even though the statement currently executing is in uspProcedure2

Both scripts create a procedure which does the same thing - sp_dba_showline is for sql 2000 with sp3 or greater installed and sp_dba_showline2005 is for sql 2005. The reason for the difference is the sql 2000 version uses fn_get_sql (a function introduced in sp3) which is being depreciated in favor of sys.dm_exec_sql_text (available in sql 2005). The sql 2005 version also uses a cross apply join to get the data.

 

The procedure takes 1 parameter, @spid, and the results returned are:

the results of dbcc inputbuffer to see what query or procedure was called from the client

the results of sp_who2 to show general process information

the first 100 lines of the procedure or query being executed (this can be useful to find the procedure name being executed)

the text of the statement actually being executed. 

If you create these on the master database as dbo, then they should be accessible from all user database


Filed under:

Comments

No Comments