Right-click on a database in SSMS and choose delete. In the dialog, check the checkbox for "Close existing connections." Click the Script button at the top of the dialog.
Using SQL Server Management StudioSelect the Connections node. Under Connections, in the Max number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL Server. Restart SQL Server.
To disconnect the connection after the query completes, from the menus go to Tools > Options > Query Execution > SQL Server > Advanced and select "Disconnect after the query executes". By checking this option, after the query executes the database connection will be disconnected.
You can disconnect everyone and roll back their transactions with:
alter database [MyDatbase] set single_user with rollback immediate
After that, you can safely drop the database :)
Go to management studio and do everything you describe, only instead of clicking OK, click on Script. It will show the code it will run which you can then incorporate in your scripts.
In this case, you want:
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
According to the ALTER DATABASE SET documentation, there is still a possibility that after setting a database to SINGLE_USER mode you won't be able to access that database:
Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.
So, a complete script to drop the database with existing connections may look like this:
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]
I know it's too late but may be its helps some one. on using this take your database offline
ALTER DATABASE dbname SET OFFLINE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With