Stored procedures easily filter SQL Server connections

In this article, we discuss the system stored procedures by using a routine to list information about SQL Server connections or performing actions on a selected connection.

SQL Server database administrators need to stay alert to connections on the server at all times. For the application of a pattern, administrators need to monitor these connections, list the connections that have problems, then take action to trim them, or perform actions on a selected connection, such as ending a connection. It's difficult, but not impossible, to perform these tasks using Enterprise Manager (Enterprise Manager). A busy database administrator needs a more convenient tool.

The SQL code in Listing 1 creates a system stored procedure with the name Sp_listconnections.

This sp_listconnections stored procedure accepts 8 parameters, but is not required.

The first parameter (@DBUltra) is optional and can be used to indicate whether a limit list is required to show only those connections that are blocked. A value of 0 indicates that all eligible connections should be listed. The value of 1 indicates that only blocked and blocked connections are listed.

The second parameter (@PCUltra) is optional and is used to specify whether the list needs to be restricted to show only those active connections (T-SQL statements are being processed.) A value of 0 indicates that all eligible connections should be listed. A value of 1 indicates that only active connections are displayed.

The next four parameters are optional and are used together to form a combination of names as key criteria for selection. Please refer to my previous article to see how I explain how these parameters work. These parameters specify the database, application, login, or client that you want to consider.

The seventh parameter (@DBTrain) is optional and is used to specify how the preceding four parameters should be applied. The D value indicates that a database is selected by name by using these parameters. A value indicates that the application is selected by name by using these parameters. The L value indicates that the login name is selected using these parameters. Other values indicate that the client's name is selected by using these parameters.

The last parameter (@PCTrain) is optional and can be used to specify what action is performed on a selected connection. This can be done by executing T-SQL code on each connection. You can also send messages to the client. The client that receives the message is the client that is included in the other parameters. All @dbtrain values are valid, and only one message is sent to any one client.

If the value of the @pctrain includes the string "@ @SPID", assume it is T-SQL code. After the "@ @SPID" is replaced with the current connection ID, T-SQL code executes on each connection.

If the value of the @pctrain is a simple text message, the message is transferred to the client. The message may not contain CR/LF characters.

If the @pctrain number is numeric, the number is assumed to be the error number of SQL Server, and the appropriate information is sent from the sysmessages table to the client. Customer messages can be added to the sysmessages table by using the sp_addmessage system stored procedure (see MSDN for more information).

Note: The ability to send messages to clients depends on the Windows Messaging service. This service on the machine and client of SQL Server must be started, and the sp_listconnections stored procedure returns information about SQL Server connections or performing an operation on a connection. The connection is filtered through the @dbultra, @PCUltra and other parameters. If the specified operation provided by @pctrain is performed, the collection of connection information is not returned.

I recommend using the user options under the Tools menu in Query Analyzer to establish the correct stored procedure call, and then it can be performed with a simple keyboard combination. The screenshot below illustrates my suggestion.

User options

Note that in the following example, the format of a Web page may cause a parameter value to go to the second row. If this is the case, remove the extra cr/lf before executing the code.

The following example lists the information that is involved in a blocked connection.

EXECUTE sp_listconnections 1

This example lists the connection information through the SQL Agent task system.

EXECUTE sp_listconnections 0,0,null,null, ' sqlagent% ', NULL, ' A '

This example lists all the active connections on the Northwind database.

EXECUTE sp_listconnections 0,1,null,null, ' Northwind ', NULL, ' D '

This example adds a customer message to the sysmessages table.

EXECUTE sp_addmessage 50001,16,n ' The server is restarted in minutes. '

This example sends a client message to all computers connected to the server.

EXECUTE sp_listconnections @PCTrain = ' 50001 '

This example sends a text message to the computer where the connection to the Northwind database is located.

EXECUTE sp_listconnections 0,0, ' Northwind ', Null,null,null, ' D ', ' The Northwind database'll go to offline in minutes. '

This example interrupts the connection to the Northwind database.

EXECUTE sp_listconnections 0,0, ' Northwind ', Null,null,null, ' D ', ' KILL @ @SPID '

I want this system stored procedure to be useful to you.