This tip continues the system stored procedure series with a routine to list information about SQL Server connections or perform actions for selected connections.

A SQL Server database administrator needs to be aware of connections to the server.  Sometimes a DBA must monitor connections to watch for certain patterns of usage.  Sometimes a DBA needs to list connections with problems so that corrective action can be taken.  Sometimes a DBA wants to perform an action for selected connections, such as terminating the connections.  It's difficult, if not impossible, to perform tasks like these using the features of Enterprise Manager.  A busy DBA needs a more convenient tool.

The SQL code in Listing 1 creates a system stored procedure named sp_ListConnections.

The sp_ListConnections stored procedure accepts eight parameters, but none of them are required.

The first parameter (@DBUltra) is optional and it specifies whether to limit the list to only connections that are involved in blocking.  A value of zero (0) means all eligible connections should be listed.  A value of one (1) means only blocked and blocking connections should be listed.

The next parameter (@PCUltra) is optional and it specifies whether to limit the list to only connections that are active (processing a T-SQL statement).  A value of zero (0) means all eligible connections should be listed.  A value of one (1) means only active connections should be listed.

The next four parameters are optional and they work together to form a combination of selection criteria using names.  Please refer to my earlier <A HREF="http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1109458,00.html">tip</A> for an explanation of how these parameters work.  These parameters specify the databases, applications, logins, or client computers to be considered.

The next parameter (@DBTrain) is optional and it specifies how to apply the previous four parameters.  A value of "D" uses the parameters to select by database name.  A value of "A" uses the parameters to select by application name.  A value of "L" uses the parameters to select by login name.  Any other value uses the parameters to select by client computer name.

The last parameter (@PCTrain) is optional and it specifies an action to perform for the selected connections.  The action can be executing T-SQL code for each connection.  The action can also be sending a message to the client computers.  The client computers to receive the message are those that are included by the other parameters.  All values of @DBTrain are valid and only one message is sent to any particular client computer.  

If the value of @PCTrain contains the string "@@SPID" then it's assumed to be T-SQL code.  The T-SQL code is executed for each connection after replacing "@@SPID" with the current connection ID.

If the value of @PCTrain is a simple text message then the message will be sent to the client computers.  The message may not contain CR/LF characters.

If the value of @PCTrain is numeric then it's assumed to be a SQL Server error number and the corresponding message from the sysmessages table will be sent to the client computers.  Custom messages can be added to the sysmessages table using the sp_addmessage system stored procedure (see <A href=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8zdx.asp>MSDN</A> for details).

NOTE: The ability to send messages to client computers depends on the Windows Messenger service.  The service must be available on the SQL Server box and on the client computers.

The sp_ListConnections stored procedure returns information about SQL Server connections or performs an action for selected connections.  The connections are filtered according to @DBUltra, @PCUltra, and the other parameters.  If @PCTrain is provided the action it specifies is performed instead of returning a result set of connection information.

I recommend using the Customize option under the Tools menu of Query Analyzer to set up an appropriate call of this stored procedure so it can be executed with a simple keyboard combination.  This <A HREF="Customize.gif">screen image</A> demonstrates the suggestion.

Please be aware that Web page formatting may have made a parameter value wrap to a second line in the examples below.  If so, remove the extra CR/LF before executing the code.

This example lists information for connections involved in blocking.

EXECUTE sp_ListConnections 1

This example lists information for connections by the SQL Agent job system.

EXECUTE sp_ListConnections 0,0,NULL,NULL,'SQLAgent%',NULL,'A'

This example lists information for active connections to the Northwind database.

EXECUTE sp_ListConnections 0,1,NULL,NULL,'Northwind',NULL,'D'

This example adds a custom message to the sysmessages table.

EXECUTE sp_addmessage 50001,16,N'The server will be restarted in 10 minutes.'

This example sends a custom message to all computers with connections to the server.

EXECUTE sp_ListConnections @PCTrain = '50001'

This example sends a literal message to computers with connections to the Northwind database.

EXECUTE sp_ListConnections 0,0,'Northwind',NULL,NULL,NULL,'D','The Northwind database will go offline in 10 minutes.'

This example kills connections to the Northwind database.

EXECUTE sp_ListConnections 0,0,'Northwind',NULL,NULL,NULL,'D','KILL @@SPID'

I hope you find this system stored procedure to be useful.