Offene Verbindungen zu einer Microsoft SQL Server-Datenbank schließen

Ab und an steht man vor der Herausforderung eine Datenbank in einem Microsoft SQL Server wiederherzustellen oder zurückzusetzen. Dann steht man oft vor dem Problem, dass noch offene Verbindungen zur Datenbank bestehen, die diese Aufgabe verhindern. Wir zeigen, wie man die offenen Verbindungen erkennen und schließen bzw. trennen kann.

Die Datenbank-Verbindungen zu einem Microsoft SQL Server können auf mehrere Wege getrennt bzw. geschlossen werden. In dem ersten Beispiel zeigen wir, wie man mit dem SQL Server Management Studio die Verbindungen mittels Menüeintrag trennen kann.

Im zweiten Beispiel zeigen wir, wie man mittels T-SQL Stored Procedure die Verbindung zu einer Datenbank trennen bzw. schließen kann.

Abschließend gehen wir auf die Vorteile bzw. Nachteile der jeweiligen Beispiele ein und erklären, warum die Verwendung von SQL Cursor vermieden werden sollte.

SQL Verbindungen mit dem SQL Server Management Studio (SSMS) schließen

Mit Hilfe des SQL Server Management Studios kann man über das Kontextmenü alle Verbindungen zur Datenbank schließen, in dem man die Datenbank "offline" schaltet:

So bald die Datenbank "offline" ist, kann sie auch nicht mehr angesprochen werden. Das kann zum Nachteil werden, wenn man danach sofort mit einigen Wartungsarbeiten beginnen möchte.

SQL Verbindungen zu einer Datenbank mit einer T-SQL Stored Procedure trennen

Ein weiterer Weg alle Verbindungen zu einer Datenbank zu trennen, ist mit dem Einsatz einer Stored Procedure möglich, die (ganz wichtig) nicht auf einem Cursor basiert:

CREATE PROCEDURE [dbo].[usp_KillAllConnectionsToDatabase](@DatabaseName nvarchar(25))
AS
BEGIN
	SET NOCOUNT ON;
	-- Variablen deklarieren
	DECLARE @dbid int, @MinSpid int, @MaxSpid int
	DECLARE @SqlCommand nvarchar(25)
	-- Variablen initialisieren
	SELECT @dbid = [dbid] FROM [master]..sysdatabases WHERE [name] = @DatabaseName;
	SELECT @MinSpid = MIN(spid) FROM [master]..sysprocesses WHERE [dbid] = @dbid;
	SELECT @MaxSpid = MAX(spid) FROM [master]..sysprocesses WHERE [dbid] = @dbid;
	-- While-Schleife zur Schließung der Verbindungen zur Datenbank einsetzen
	WHILE @MinSpid <= @MaxSpid
	BEGIN
		SET @SqlCommand = N'KILL ' + CAST(@MinSpid AS NVARCHAR(5));
		EXEC(@SqlCommand);
		SET @MinSpid=@MinSpid+1;
	END
END

Warum sollte man SQL Cursor nicht einsetzen? Don't use SQL Server Cursor!

Während unseren Recherchen haben wir viele Artikel mit dem Thema "Trennung von Verbindungen zu einer Datenbank" gesehen, wo ein SQL Cursor eingesetzt wird. Microsoft selbst empfiehlt Cursor nicht einzusetzen, da bessere Alternativen existieren, siehe Artikel: Increase your SQL Server performance by replacing cursors with set operations.

Des Weiteren sind Cursors oft das falsche Werkzeug für die falsche Aufgabe. Sie werden für die schnelle und schmutzige Programmierung verwendet, wenn ein Datenbankentwickler kein gutes Verständnis für Mengenoperationen hat.

Zum Beispiel ist eine Datenbankoperation manchmal am besten auf der Client-Seite und nicht auf der Server-Seite durchzuführen. Serverseitige Cursor wurden in ADO unterstützt, die eine schreibgeschützte Ansicht der verwendeten Daten erforderten.

ADO.NET verwendet den Datenleser oder Datenadapter, der die getrennte Clientseite betreibt. Der Datenadapter greift auf die Datenbank zu und bringt die Daten unmittelbar auf die Client-Seite, trennt sofort die Verbindung zum Server, das wiederum zu einer besseren Leistung und Skalierbarkeit führt.

Auf diese Weise kann der Client durch die auf der Webseite oder dem Webserver eingestellten Ergebnisse blättern, anstatt die auf dem SQL Server eingestellten Ergebnisse zu durchsuchen und so Ressourcen zu verbrauchen.

Bevor man einen SQL Cursor einsetzt, sollten man festlegen, wie die Daten zukünftig verwendet werden. Auch sogenannte SQL-Batches lassen sich heutzutage besser mit einer WHILE-Schleife als mit einem SQL-Cursor realisieren.