Reliably Drop A Database In T-SQL Script

 

I read with interests about Greg Low’s post “Reliably Dropping a Database in a T-SQL Script is Too Hard”, I think I have a solution, which at least I cannot prove it not working so far.

   1: USE master; 

   2: GO

   3:  

   4: IF EXISTS(SELECT 1 FROM sys.databases WHERE name = N'Blah')

   5: BEGIN 

   6:     use [Blah]

   7:     ALTER DATABASE Blah SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- all OTHER sessions inside [Blah] will be closed

   8:  

   9:     alter database [Blah] set recovery full;

  10:     backup database [Blah] to disk='NUL' with init; 

  11:     use master;

  12:     backup log [Blah] to disk='NUL' with norecovery; -- [Blah] db will be in restoring mode

  13:  

  14:     DROP DATABASE [Blah]; 

  15: END; 

  16: GO

  17:  

There are two points worth mentioning here:

1. Before setting the database [Blah] to single_user mode, we need to be inside [Blah] (line 6), this way, all other sessions will be killed by line 7

2. Line 12 backup log with norecovery will set the database [Blah] to restoring mode and so no other sessions can access it.

Note, this code is for illustration and easy-explanation only, in real world, line 6 to 14 (i.e. the IF block) should be inside a dynamic sql string, otherwise, if you do not have [Blah] database in the first place, you will get the error.

About these ads
This entry was posted in Administration, Operation Productivity and tagged . Bookmark the permalink.

2 Responses to Reliably Drop A Database In T-SQL Script

  1. Greg Low says:

    Good thought but it’s got the same race condition. In between the change to master and the drop, another connection could appear. The drop would then fail.

    • jxhyao says:

      Actually, I did not elaborate too much in my original post. But my test shows:
      I have two sessions, session A and Session B I do the following in sequence:
      – session B starts –
      Use [Blah] — Session B connects to [Blah]
      – session B ends –

      – session A starts
      Use [Blah]
      alter database [Blah] to single_user with rollback immediate;
      Use [Master]
      – session A ends

      I will find that session B is killed (just as expected)
      Now if we open a new session C connecting to [master] db by default, and in Session C, I cannot run the following:

      use [Blah]

      I will get error indicating only [Blah] db cannot be connected because another user is already using it.

      However, back to Session A, I can run
      use [Blah]

      i.e. I can switch back to [Blah] in Session A, but not have the capability to do so in other sessions.

      With this “fun” fact, I come up with my solution in the original post and I have tested multiple times. With your original script, I indeed can counter the issue you mentioned, but with my solution, I did not. :-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s