Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dropping a database – such a simple thing!

G’day,

I think it’s a safe bet that everybody here has issued a DROP DATABASE statement and knows what to expect.

Well, today I was experimenting with a development database. The script I was using employed simply reusable code to drop and re-create the database – or so I though.

The code I was using was a follows

USE tempdb;
GO

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name = 'TestDB')
BEGIN
PRINT 'Database does not exist at present';
END
ELSE
BEGIN
PRINT 'Database exists and will be dropped';
ALTER DATABASE TestDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;
END;
GO

CREATE DATABASE TestDB;
GO

Upon re-running this code – which I assumed would both drop the database AND remove the physical data and log files, I received an error informing me that the files could not be created because they already existed.

Strange, I though and I re-ran the code several times.

Then I noticed that I had written

ALTER DATABASE TestDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;

instead of

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;

A rather subtle difference – however one that, as I found, makes a huge difference.
You see, when a database is normally dropped using a DROP statement the following two things happen

  1. The database is deleted from the server – ie the entry in sys.databases is removed.
  2. The physical data and logs files are removed.

However in my case, because I first set the database to OFFLINE the physical files on disk were not removed.

Books Online was very clear about the difference in behavior for a database in an OFFLINE state.

And it is good to now be totally clear on the difference because in certain situations – such as development – we might not care about the database, so deleting it constantly may be ok, so the following code may apply

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestDB;

However, there are certain times when we may want the database files to hang around after deleting the database.
Such times could include, when removing a production database and we wish to keep the physical files as another form of backup (I’d always additionally take a traditional backup – as well as verifying the backup is good by physically restoring it to another server) or when wanting to rename the physical files of a database.

Additionally, we should note that if the database is online, but certain files are OFFLINE then those files will not be deleted either.

anyway, that was just my piece of wisdom for the day that I thought I’d share with you all.

Have a nice day.

cheers

Martin.

Comments

Posted by Anonymous on 19 October 2011

Pingback from  Dew Drop – October 19, 2011 | Alvin Ashcraft's Morning Dew

Posted by Yokers on 21 October 2011

Interesting - we use a NetApp FlexClones to present new drives every half hour and usually my preferred method is to ALTER DATABASE [] SET OFFLINE as it kills two birds with one stone (allows me to drop without using sp_removedbreplication & not bother setting single user). Luckily since we do present new drives every 30 we wouldn't every encounter this but it's well worth knowing.

Good post, thanks Martin,

Rik

Posted by kstjacques on 21 October 2011

Thank you, Martin.  I learned from this article and now I'm hoping to learn a bit more :)

Sorry, I haven't been a DBA very long so I don't understand the implications of dropping a database without "ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;".  What are the advantages of issuing that command before dropping the database?

Thanks,

Kim :)

Posted by Yokers on 21 October 2011

Setting it to single user mode allows you exclusive access to the database therefore nobody can block you when attempting to drop it.

The link Martin included is quite helpful, gives you all the info you might need including the answer to your question :)

Posted by kstjacques on 21 October 2011

Oh yeah... look at that :D

"You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER."

ha!  thank you for responding :)

Leave a Comment

Please register or log in to leave a comment.