A bit of old school SQL Server 2000 stuff that I had to delve in to, to resuscitate a dying database using sp_who2.
Running the command sp_who2 gives details of the current sessions, users and processes running in an instance of SQL Server. It's an undocumented version of the sp_who command (http://msdn.microsoft.com/en-us/library/ms174313.aspx) but sp_who2 gives a bit more info as to what certain processes are doing. From this, you can get a feel for what sessions and processes are hanging the database, or are being blocked, or just generally causing problems in the current running sessions of the instance of SQL Server.
Once the SPID (Server Process ID) is known that is causing issues, this can be used to get the sql_handle from sysprocesses. The sql_handle is a binary key that references queries in the procedure cache.
After getting the sql_handle, the function fn_get_sql can be used to reveal the SQL statement that seems to be causing issues.
DECLARE @Handle varbinary(255);
SELECT
@Handle = sql_handle
FROM
sysprocesses
WHERE
spid = SPID-from-sp_who2-to-go-here;
SELECT * FROM ::fn_get_sql(@Handle);
GO
For later versions of SQL Server (i.e. SQL Server 2005 and SQL Server 2008), use sys.dm_exec_requests and sys.dm_exec_sql_text to figure things out.
Running the command sp_who2 gives details of the current sessions, users and processes running in an instance of SQL Server. It's an undocumented version of the sp_who command (http://msdn.microsoft.com/en-us/library/ms174313.aspx) but sp_who2 gives a bit more info as to what certain processes are doing. From this, you can get a feel for what sessions and processes are hanging the database, or are being blocked, or just generally causing problems in the current running sessions of the instance of SQL Server.
Once the SPID (Server Process ID) is known that is causing issues, this can be used to get the sql_handle from sysprocesses. The sql_handle is a binary key that references queries in the procedure cache.
After getting the sql_handle, the function fn_get_sql can be used to reveal the SQL statement that seems to be causing issues.
DECLARE @Handle varbinary(255);
SELECT
@Handle = sql_handle
FROM
sysprocesses
WHERE
spid = SPID-from-sp_who2-to-go-here;
SELECT * FROM ::fn_get_sql(@Handle);
GO
For later versions of SQL Server (i.e. SQL Server 2005 and SQL Server 2008), use sys.dm_exec_requests and sys.dm_exec_sql_text to figure things out.
Comments
Post a Comment