Stored procedure: Easily filter for SQL Server connections

This tip continues our ongoing system stored procedure series with a routine to list information about SQL Server connections or perform actions on selected connections. Click to go directly to the sp_ListConnections routine or view the complete list of stored procedures, updated twice monthly.

A SQL Server database administrator needs to be aware of connections to the server. He may need to monitor those connections for certain patterns of usage; list connections with problems so corrective action can be taken; or perform actions on selected connections, such as terminating the connections. It's difficult, if not impossible, to perform tasks like these using 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 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 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 work together to form a combination of selection criteria using names. Please refer to my earlier tip 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 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 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 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 MSDN 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 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 for this stored procedure, so it can be executed with a simple keyboard combination. This screen image demonstrates the suggestion.

Customize option

Please be aware that Web page formatting may make 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.

Click for the stored procedure: sp_ListConnections

About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.

More information from SearchSQLServer.com