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)

DBA Tools: sp_who3

By Mircea Nita,

Assuming you have a large number of databases with hundreds of active users on a server, it is often very difficult to follow a blocking chain that affects one of the databases, since sp_who2 returns all the processes for all the databases available.

This is the reason why I have modified sp_who2 and the new stored procedure called sp_who3 limits its scope only to the database name passed as parameter, or databases that match the substring in the parameter.

Without a parameter sp_who3 returns a listing similar to the output of sp_who2 ordered by SPID. (exec sp_who3)

Another way of using sp_who3 is passing a database name as parameter. The stored procedure will return only those user processes relating to the specified database. It is therefore easy to follow a blocking chain for example. (exec sp_who3 'DatabaseName').

You can also use sp_who3 by passing a part of the database name as parameter.

This is particularly useful if for example you have a large number of Test databases and a large number of Beta databases on the same server and you have a consistent naming convention, like <DatabaseName>Test and <DatabaseName>Beta.

You can use sp_who3 to display user processes relating to a database subset of all databases available on the server like this: exec sp_who3 'Beta'

This is what I use to rapidly find out which users are logged on to the Beta system. This allows me to see at a glance whether the logged users are just backend services, or if real operators use the Beta system for testing.

 

I hope that you will find sp_who3 useful and easy to use.

 

Total article views: 3886 | Views in the last 30 days: 68
 
Related Articles
FORUM

New SP_WHO3

A little tweak to the original sp_who2 with much more useful info

ARTICLE

Sending a Variable Number of Elements in a Parameter

What are your options for sending a variable number of choices in a parameter to a stored procedure?...

FORUM

DBA Tools:sp_who3

Comments posted to this topic are about the item [b][url=/scripts/sp_who3/69906/]DBA Tools: sp_who3[...

FORUM

DATABASE NAME AS PARAMETER IN PROCEDURE

DATABASE NAME AS PARAMETER IN PROCEDURE

SCRIPT

Another sp_who3

Get processes run and the command.

Tags
blocking chain    
sp_who3    
 
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