Database in use

Question

Database in use

dan-572483

SSCoach

Points: 15870
More actions
August 18, 2015 at 4:33 pm

#398920

We have a weekly job that restores a database from a copy downloaded from a vendor. It occasionally fails with the error:
Msg 3101, Level 16, State 1, Line 9
Exclusive access could not be obtained because the database is in use.
I thought this was because the job did not set the DB to single user mode to kill existing connections, but the job contains the syntax:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET MULTI_USER
RESTORE DATABASE [dbname] FROM DISK = ...
My thought is that in the few milliseconds between the SET MULTI USER taking effect and the RESTORE command taking control of the DB, another process connects to the DB, preventing RESTORE from obtaining exclusive access.
1) What is the purpose of the SET MULTI_USER command here? If the copy of the DB was in multi-user mode when the backup was made, it will restore as multi-user right?
2) If I instead use the syntax below to set Single_user while connected to the DB in question, and skip setting it to multi_user, will that resolve the issue?
USE [dbname]
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
RESTORE DATABASE [dbname] FROM DISK = ...

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

dan-572483 (8/18/2015)
We have a weekly job that restores a database from a copy downloaded from a vendor. It occasionally fails with the error:
Msg 3101, Level 16, State 1, Line 9
Exclusive access could not be obtained because the database is in use.
I thought this was because the job did not set the DB to single user mode to kill existing connections, but the job contains the syntax:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [dbname] SET MULTI_USER
RESTORE DATABASE [dbname] FROM DISK = ...
My thought is that in the few milliseconds between the SET MULTI USER taking effect and the RESTORE command taking control of the DB, another process connects to the DB, preventing RESTORE from obtaining exclusive access.
1) What is the purpose of the SET MULTI_USER command here? If the copy of the DB was in multi-user mode when the backup was made, it will restore as multi-user right?
2) If I instead use the syntax below to set Single_user while connected to the DB in question, and skip setting it to multi_user, will that resolve the issue?
USE [dbname]
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
RESTORE DATABASE [dbname] FROM DISK = ...

That should resolve it. I would alter that database from a connection to the master database though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

dan-572483 SSCoach Points: 15870 More actions · Answer 2

But wouldn't altering it from master open the possibility of a another connection coming before the RESTORE command takes exclusive access?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You run that risk as it is.

It works about the same either way with about the same amount of risk of another connection stealing the connection.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

I prefer to avoid all that silliness and set DBs offline with rollback immediate prior to restoring on top of them. I'm quite confident no one else is connecting then 🙂

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Jacob Wilkins (8/19/2015)
I prefer to avoid all that silliness and set DBs offline with rollback immediate prior to restoring on top of them. I'm quite confident no one else is connecting then 🙂

Same here. I also want to warn that having a database set to single user is dangerous because if you lose the connection, something else could pick up the single available connection and you play hell getting it back.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jasona.work SSC Guru Points: 50117 More actions · Answer 6

Another vote for setting to offline, rather than single_user. Garaunteed no one is going to get in before you start the restore, and the DB will be back to online when the restore finishes.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 7

I will usually connect to master, kill all connections to the database in step 1 and then start my restore in step 2.

I've never been blocked when I've run this. I've attached the kill script I use. Takes the database name as a parameter and loops through sysprocesses.

Tom

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

Jeff Moden (8/19/2015)
Jacob Wilkins (8/19/2015)
I prefer to avoid all that silliness and set DBs offline with rollback immediate prior to restoring on top of them. I'm quite confident no one else is connecting then 🙂
Same here. I also want to warn that having a database set to single user is dangerous because if you lose the connection, something else could pick up the single available connection and you play hell getting it back.

Instead of setting database to SINGLE_USER, consider setting it to RESTRICTED_USER. That should block application and ad-hoc user accounts while allowing the DBA access.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Eric M Russell (9/24/2015)
Jeff Moden (8/19/2015)
Jacob Wilkins (8/19/2015)
I prefer to avoid all that silliness and set DBs offline with rollback immediate prior to restoring on top of them. I'm quite confident no one else is connecting then 🙂
Same here. I also want to warn that having a database set to single user is dangerous because if you lose the connection, something else could pick up the single available connection and you play hell getting it back.
Instead of setting database to SINGLE_USER, consider setting it to RESTRICTED_USER. That should block application and ad-hoc user accounts while allowing the DBA access.

In theory that is great. Sadly, too many vendor (and in-house built) application accounts are granted sysadmin or dbo access.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Eric M Russell SSC Guru Points: 125561 More actions · Answer 10

If any of the application or user accounts have sysadmin membership, then that complicates the issue. However, another option to mitigate that is to alter login status to disabled for all application and user accounts prior to the maintenance operation, and then re-enable them again afterward. Depending on the use case of the database server, it's probably cleaner to not even let them login during the maintenance window.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Eric M Russell (9/28/2015)
it's probably cleaner to not even let them login during the maintenance window.

Agreed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events