Как указать "закрыть существующие соединения" в sql script

Я активно работаю над своей схемой в SQL Server 2008 и часто хочу перезапустить мою базу данных drop/create script. Когда я запустил

USE [master]
GO

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

Я часто получаю эту ошибку

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

Если вы щелкните правой кнопкой мыши по базе данных в панели проводника объектов и выберите "Удалить задачу" из контекстного меню, появится флажок "закрыть существующие соединения"

Есть ли способ указать этот параметр в моем script?

Ответ 1

Вы можете отключить всех и отменить свои транзакции с помощью:

alter database [MyDatbase] set single_user with rollback immediate

После этого вы можете безопасно удалить базу данных:)

Ответ 2

Перейдите в студию управления и сделайте все, что вы описали, только вместо того, чтобы нажимать OK, нажмите Script. Он покажет код, который он будет запускать, который вы затем можете включить в свои скрипты.

В этом случае вы хотите:

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Ответ 3

Согласно документации ALTER DATABASE SET, все еще существует вероятность того, что после установки базы данных в режим SINGLE_USER вы не сможете получить доступ к этой базе данных:

Перед установкой базы данных в SINGLE_USER убедитесь, что для параметра AUTO_UPDATE_STATISTICS_ASYNC установлено значение OFF. Когда установлено значение ON, фоновый поток, используемый для обновления статистики, принимает соединение с базой данных, и вы не сможете получить доступ к базе данных в однопользовательском режиме.

Итак, полный script для удаления базы данных с существующими подключениями может выглядеть следующим образом:

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]

Ответ 4

Я попробовал, что hgmnz сказал на SQL Server 2012.

Управление, созданное для меня:

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

Ответ 5

Я знаю, что это слишком поздно, но может быть, это помогает кому-то. при использовании этого откройте свою базу данных в автономном режиме

ALTER DATABASE dbname SET OFFLINE