Thursday, April 9, 2009

Explicitly Control SQL Server Connections

There is many times where we might want to explicitly control the connections to an instance of SQL Server and more specifically to a database.

Such scenarios might include:
  • A restore procedure of a backup set which cannot complete as there are still active connections to the specific database.
  • You need to drop a database (yes I know, this sounds strange :) ) and you cannot do it as there are still active connections to that database.
  • … and generally you want to perform an operation on a database which requires a single user mode but there are other active connections as well and you want to terminate them.
Even though there is more than one method for handling the active connections to a database, here I will present one that I consider very efficient as it allows the DBA to get information about all the active connections to a specific database and take direct actions.

The important table in this case is sysprocesses which exists in the master database. This table is of great significance as it contains valuable information. Sysprocesses has a separate row for each unique SPID in a SQL Server connection. A SQL Server connection might be among other:
  • A query window connected to a specific database
  • Reporting services
  • Analysis Services
  • Management Studio
  • Any other database application (client) connected to a database.
The question here is: how can the sysprocesses table help us explicitly terminate a SQL Server connection?

The answer is not that complex :)

By issuing a query against the sysprocesses table, we are able to find all the connections to a given database, get their SPIDs and then by using the SQL Server command named KILL, forcibly terminate these connections.

As an example, consider that we have a database called TestDB. On this database, let’s say that we have performed a task with SSMS a while ago (i.e. Select Top 1000 Rows) and we have also opened a query window that uses TestDB.

For getting the SPIDs along with some other useful information we execute the following query:

SELECT spid,login_time,program_name,nt_domain,nt_username
FROM master..sysprocesses
WHERE DBID=DB_ID('TestDB')

And here are the results of the query:

spid | login_time | program_name | nt_domain | nt_username
51 | 2009-04-09 21:33:49.193 | Microsoft SQL Server Management Studio | TESTING | User1
53 | 2009-04-09 23:01:17.050| Microsoft SQL Server Management Studio - Query | TESTING | User1

So, what do the above results mean? Well, in plain words we can see that there are two open connections to the TestDB database. The connection with SPID 51 comes from the SQL Server Management Studio (SSMS) and the connection with SPID 53 is actually a Query window connected to the database. Also, the login_time displays the exact time each connection was established, the program_name displays the application or SQL Server module/tool which uses the connection, the nt_domain displays the Windows domain for the database client and the nt_username is the Windows user name used by the client (in the case where Windows Authentication is used).

Sysprocesses contains much more information than the columns used in the above query. Such information can be: memory usage information, disk I/O information, transactional information, network information, etc. To this end, besides of using sysprocesses for finding an SPID, you can also use it for performance tuning and other operations as it can provide important information about the current usage of the SQL Server instance and specific databases.

MSDN Documentation for the sysprocesses table in SQL Server 2000 can be found here, for SQL Server 2005 here, and for SQL Server 2008 here.

And now back to our case scenario.

If we are in a scenario like the ones I described in the beginning of this post, or in a similar case where we want to terminate active SQL Server connections that prevent us from executing another task (i.e. SPIDs 51 and 53), then an option is to first find these SPIDs (using the sysprocesses table) and then issue the SQL Server KILL command.

So, now that we have the SPIDs of the two connections, we can terminate them with the following statements:

KILL 51;
KILL 53;

Important: After terminating the connections-SPIDs, you cannot use the specific “programs” associated with those SPIDs (i.e. Query window) because the connections do not exist anymore. In order to open a new connection you have to open a new Query window for an example, or reconnect a database application, etc. In the opposite case, where you might try to access the terminated connections, you will get the following error message:

“A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)”

The above error message was also explained in an older post.

I hope you found this post useful!
[Ads]
Check out my latest eBooks on SQL Server:
Administering SQL Server - Ebook Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: