Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 2119 | Views in the last 30 days: 3
 
Related Articles
FORUM

Attribute Key cannot be found when processing cube

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

FORUM

order by in a parameter

Order the data found Ascending

BLOG

View SQL Server information using Transact-SQL script

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

BLOG

Which Process ID is SQL Server running on?

There was an interesting question posted on a forum recently, asking how can you tell which sqlservr...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones