Archive

Archive for March, 2013

BI SQL # 110 : SQL Server Script : Drop Database script without getting affected by any running queries on same Database

March 29, 2013 Leave a comment

Hi friends image

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 DBimage

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

image

Also we cant even directly delete through SSMS

image

By Simply right Click and Delete Database

image

It will also show same error

image

Now lets learn how to get rid of this error :

Dropping Database through SSMS

While deleting through SSMS just remember to click following option

image

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
GO

DECLARE @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 :

image

    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 :

Link Resource Website

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

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
%d bloggers like this: