Comment spécifier "fermer les connexions existantes" dans un script SQL

Question

Je développe activement mon schéma dans SQL Server 2008 et je souhaite fréquemment réexécuter mon script de dépôt / création de base de données. Quand je cours

 USE [master]
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDatabase')
DROP DATABASE [MyDatabase]
GO
 

J'ai souvent cette erreur

 Msg 3702, Level 16, State 4, Line 3
Cannot drop database "MyDatabase" because it is currently in use.
 

Si vous cliquez avec le bouton droit sur la base de données dans le volet de l'explorateur d'objets et que vous sélectionnez la tâche de suppression dans le menu contextuel, une case à cocher permettant de "fermer les connexions existantes" est cochée.

Est-il possible de spécifier cette option dans mon script?

Answer 1

Vous pouvez déconnecter tout le monde et annuler leurs transactions avec:

 alter database [MyDatbase] set single_user with rollback immediate
 

Après cela, vous pouvez supprimer la base de données en toute sécurité :)

Answer 2

Allez dans le studio de gestion et faites tout ce que vous décrivez. Au lieu de cliquer sur OK, cliquez sur Script. Il montrera le code qu'il va exécuter que vous pourrez ensuite incorporer dans vos scripts.

Dans ce cas, vous voulez:

 ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 

Answer 3

Selon l' ALTER DATABASE ENSEMBLE de la documentation, il y a encore une possibilité qu'après la définition d'une base de données en mode SINGLE_USER vous ne serez pas capable d'accéder à la base de données:

Avant de vous définissez la base de données SINGLE_USER, vérifiez l'option AUTO_UPDATE_STATISTICS_ASYNC est réglé sur OFF. Lorsque vous l'activez, le thread d'arrière-plan utilisée pour mettre à jour les statistiques se connecte à la base de données, et il vous sera impossible d'accéder à la base de données en mode mono-utilisateur.

Ainsi, un script complet pour supprimer la base de données avec des connexions existantes peuvent ressembler à ceci:

DECLARE @dbId int
DECLARE @isStatAsyncOn bit
DECLARE @jobId int
DECLARE @sqlString nvarchar(500)

SELECT @dbId = database_id,
       @isStatAsyncOn = is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'db_name'

IF @isStatAsyncOn = 1
BEGIN
    ALTER DATABASE [db_name] SET  AUTO_UPDATE_STATISTICS_ASYNC OFF

    -- kill running jobs
    DECLARE jobsCursor CURSOR FOR
    SELECT job_id
    FROM sys.dm_exec_background_job_queue
    WHERE database_id = @dbId

    OPEN jobsCursor

    FETCH NEXT FROM jobsCursor INTO @jobId
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @sqlString = 'KILL STATS JOB ' + STR(@jobId)
        EXECUTE sp_executesql @sqlString
        FETCH NEXT FROM jobsCursor INTO @jobId
    END

    CLOSE jobsCursor
    DEALLOCATE jobsCursor
END

ALTER DATABASE [db_name] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [db_name]

Answer 4

J'ai essayé ce que hgmnz a dit sur SQL Server 2012.

La direction m'a été créée:

 EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'MyDataBase'
GO
USE [master]
GO
/****** Object:  Database [MyDataBase]    Script Date: 09/09/2014 15:58:46 ******/
DROP DATABASE [MyDataBase]
GO