SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

a handy sysprocesses select

By Rik Thwaites,

Why: During day-to-day life as a DBA I've no doubt you would have come across a situation where you need to get to the bottom what a running process is doing in SQL Server, aside from the basic sp_who2 and such I wanted to get more detail information, I wanted to know enough to give direct information in a simple form to whoever I needed to, I found this handy in particular in UAT and DEV environments as I found some of our developers were quite happy to test new functions/procedures which would quite happily run for hours locking out resource.

Function: Using sys.sysprocesses which allows you to pick up some decent information about running processes I used outer applied sys.dm_exec_sql_text passing the sql_handle from sys.sysprocesses which gave me the text data for that given handle. I also used the statement start/end to get the exact point that the given process was up to (current_cmd), granted this was only really useful in multi statement batches, specifically stored procedures. You'll probably note that on a few occasions these SPID's won't have TextData available, I could've excluded these by using a cross apply but I'd personally I'd rather have a full scope of what's going on. I've thrown a basic order by in there but that's really up to you as to what order you wish to see the data, in later uses of this I found it handy to order it by any blocked processes so I could head straight to that SPID.

Enjoy, Rik.

Total article views: 2173 | Views in the last 30 days: 1
Related Articles

Attribute Key cannot be found when processing cube

Attribute Key cannot be found when processing cube even though record exists in the dimension table


order by in a parameter

Order the data found Ascending


View SQL Server information using Transact-SQL script

Recently one of our clients requested information about their SQL Server infrastructure which we man...


Catching Deadlock Information in SQL Logs

This article presents a handy way to retrieve deadlock information in an ordered way from the error ...