script pour supprimer toutes les connexions à une base de données (plus que RESTRICTED_USER ROLLBACK)

Question

J'ai une base de données de développement qui est fréquemment redéployée à partir d'un projet de base de données Visual Studio (via une construction automatique TFS).

Parfois, lorsque je lance ma compilation, j'obtiens cette erreur :

ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.  
ALTER DATABASE statement failed.  
Cannot drop database "MyDB" because it is currently in use.  

J'ai essayé ceci :

ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

mais je ne peux toujours pas supprimer la base de données. (Je pense que la plupart des développeurs ont dbo accès.)

Je peux exécuter manuellement SP_WHO et commencer à tuer les connexions, mais j'ai besoin d'un moyen automatique de le faire dans la construction automatique. (Bien que cette fois-ci ma connexion soit la seule sur la base de données que j'essaie de supprimer).

Existe-t-il un script qui peut supprimer ma base de données indépendamment de la personne connectée ?

Answer 1

Mise à jour

Pour MS SQL Server 2012 et supérieur

USE [master];

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('MyDB')

EXEC(@kill);

Pour MS SQL Server 2000, 2005, 2008

USE master;

DECLARE @kill varchar(8000); SET @kill = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('MyDB')

EXEC(@kill);

Answer 2

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Réf : http://msdn.microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx

Answer 3

Vous pouvez obtenir le script fourni par SSMS en procédant comme suit :

1. Cliquez avec le bouton droit de la souris sur une base de données dans le SSMS et sélectionnez supprimer.
2. Dans la boîte de dialogue, cochez la case "Fermer les connexions existantes".
3. Cliquez sur le bouton script en haut de la boîte de dialogue.

Le script ressemblera à ceci :

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO

Answer 4

D'après mon expérience, l'utilisation de SINGLE_USER est utile la plupart du temps, mais il convient d'être prudent : Il m'est arrivé qu'entre le moment où j'ai lancé la commande SINGLE_USER et le moment où elle s'est terminée... un autre "utilisateur" avait apparemment obtenu l'accès SINGLE_USER, et non moi. Si cela se produit, vous allez avoir du mal à récupérer l'accès à la base de données (dans mon cas, c'est un service spécifique fonctionnant pour un logiciel avec des bases de données SQL qui a obtenu l'accès SINGLE_USER avant moi). Ce que je pense être la méthode la plus fiable (je ne peux pas m'en porter garant, mais c'est ce que je vais tester dans les jours à venir), est en fait la suivante :
- arrêter les services susceptibles d'interférer avec votre accès (s'il y en a)
- utiliser le script 'kill' script ci-dessus pour fermer toutes les connexions
- paramétrer la base de données en single_user immédiatement après cela
- puis procéder à la restauration

Answer 5

J'ai testé avec succès le code simple suivant

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO