BI SQL # 110 : SQL Server Script : Drop Database script without getting affected by any running queries on same Database
Hi friends 
We really get lot of frustration during deleting Database , Such as Database use by some one and bla bla bla ….
There is lot of ways we can actually drop Database peacefully , We will try to cover all of them
In this article we are going to cover following points.
- Database Drop error
- Dropping Database through SSMS
- * Dropping Database through SQL Script *
Database Drop error
Lets start with demo Database TestDelete DB
After firings some query on DB , SQL server actually acquires some lock on it !
After exciting drop command as shown below we get following error
Drop Statement – DROP Database TestDelete
Also we cant even directly delete through SSMS
By Simply right Click and Delete Database
It will also show same error
Now lets learn how to get rid of this error :
Dropping Database through SSMS
While deleting through SSMS just remember to click following option
Now it will be dropped successfully !
Dropping Database through SQL Script
This is actually hurt of this article and my search :
Sometime we are restricted and not able to delete through SSMS.
Also sometime we have to Drop Database as per automated way of requirement !
So we need solid script which will tack care of all this.
Please don’t forget to put Database name on parameter @DBNAME
USE master
GODECLARE @DBNAME VARCHAR(50)
SET @DBNAME = ‘TestDelete’
DECLARE @HoldSql VARCHAR(500)
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = @DBNAME
SET @HoldSql = ‘ALTER DATABASE [‘ + @DBNAME + ‘] SET SINGLE_USER WITH ROLLBACK IMMEDIATE’
EXECUTE (@HoldSql)
SET @HoldSql = ‘DROP DATABASE [‘ + @DBNAME + ‘]’
EXECUTE (@HoldSql)
Executing this query will Successfully drop Database without any error as shown below :
Hope you will like this Drop Database script.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
