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)
Thank this author by sharing:
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.
Order of processing of cubes
Attribute Key cannot be found when processing cube even though record exists in the dimension table
Order the data found Ascending
Recently one of our clients requested information about their SQL Server infrastructure which we man...
This article presents a handy way to retrieve deadlock information in an ordered way from the error ...