End an ALTER DATABASE immediately if anyone is in the database.

5

June 11, 2020 by Kenneth Fisher

The development team has told me they’ve shut down the application, there are no more connections and I can go ahead and set the database to SINGLE_USER, or maybe RESTRICTED_USER before running various processes on it. I check and low and behold, no connections.

Great! I go ahead and start up my script and after 30 seconds maybe a minute later I’m starting to wonder if maybe there is in fact still a connection. I kill my script, check and yep, there’s a connection. Must have been made between the time when I checked and started my script. I check in with the development team and this time they assure me there won’t be another connection.

But there was.

If this strikes a bit too close to home for you then you need to look at the ROLLBACK clause. It’s great for killing and rolling back all of the current connections before making my change.

But this is a pretty sensitive app and if there’s something running I have to let it finish. No ROLLBACK allowed. But I’m also not going to wait forever to see if my alter is going to happen. Turns out there is a nice easy option for this too.

ALTER DATABASE Test SET RESTRICTED_USER WITH NO_WAIT

Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database ‘Test’. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.

Well, back to the development team.

5 thoughts on “End an ALTER DATABASE immediately if anyone is in the database.

  1. k3baz says:

    Two thinks I can think of, 1. The dev team is not aware of the process that is connecting and it may not be something that is under their control. 2. A dev is connecting to the database to see if it was removed yet, lol.

  2. sqldba618 says:

    if this non-prod environment and only one login keep connecting to database, I usually disable that login at the beginning of the script, wait for 10 sec, run Alter DB command.. Enable again when I’m done with my work.. of course, prod environment will be difficult to handle it.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: