x

The database could not be exclusively locked

We have an automated batch job that renames a couple of databases daily. We have TESTNew, TEST, TESTOld. TESTNew is created every day and refreshed with new information. When the process completes it renames TEST to TESTOld to keep the previous day's data. Then it renames TESTNew to TEST to keep the most up to date data.

When renaming TESTNew to TEST sometimes we get an error. The database could not be exclusively locked to perform the operation. Msg 5030, Level 16, State 2

The statement we are using in the batch job that runs the sql script is as follows: Alter database TESTNew modify name = TEST

I already tried to make the database single user but it still fails to rename occasionally. I issued sp_who2 process and I see the following process that has the database open. 11 BACKGROUND sa TESTNew CHECKPOINT 16 2

How to solve this problem? And why does the background process locks the database? Thanks for your help.

more ▼

asked Oct 19 '11 at 10:49 AM in Default

jsndvl11 gravatar image

jsndvl11
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It appears that the description of the problem is explaining itself. You can rename the database only if there are no other active connections to it. If the server is in the process of checkpointing the database then your job cannot get exclusive access to it so rename fails. I don't know why you have to do what you do, but what about if you try forcing the checkpointing yourself before renaming?

use TESTNew; go

checkpoint; go

use master; go

alter database TESTNew modify name = TEST; go
more ▼

answered Oct 19 '11 at 11:07 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

I think running checkpoint manually also will not be help because checkpoint process will also depends on how much modification are pending to process so, try to manage to rename database after some time if you are doing huge modification in database.
Oct 19 '11 at 10:23 PM Amardeep
Thanks Oleg. I'll try doing the checkpoint manually and post if it is successful. What I don't understand is that this rename problem was not happening with SQL 2000. It started when the process was moved to SQL 2005.
Oct 20 '11 at 06:42 AM jsndvl11
@sqlchamp You are probably correct, but I was just speculating that if the rename fails because the database is busy due to checkpointing process then it might mean that issuing the next checkpoint will not be able to start until the original one completes, and thus the script will just sit there wasting time and only then do the dry checkpoint which probably will execute very fast because the previous one just ended. Not sure that it will actually cure the problem, I just hope that it might...
Oct 20 '11 at 02:01 PM Oleg
Doing the checkpoint manually helped with the problem. I haven't seen the error in a week. Thanks all for the input.
Oct 28 '11 at 11:41 AM jsndvl11
(comments are locked)
10|1200 characters needed characters left

If you make sure you are setting it to single_user mode first, and you are not yourself logged onto the database, it should be fine. Of course, you may need to use "with rollback immediate" when moving it to single user mode. For instance, this script should work even if there are open transactions:

use master;

Go

alter database TestNew set single_user with rollback immediate;

GO

alter database TestNew modify name = Test ;

GO

alter database Test set multi_user;

Go

However, even that will not work if the database is unavailable for some reason other than a simple lock being maintained. For instance if it is offline it will fail with an error like:

"Msg 942, Level 14, State 4, Line 1 Database 'TestNew' cannot be opened because it is offline."

And it will also fail if it is in single user mode and some other process has a connection (or gets a connection in between when you move it to single_user mode and issue the rename command).

Also, in the scenario you are describing you might be able to avoid some of the question. You might be able to rename Test to TestOld and then just create a new Test database with your new data without ever creating and then renaming a TestNew database at all. Depending on your workflow that might cause other problems so it may not be a good solution for you, but if it does not cause other problems it may save you some steps and sidestep this issue.
more ▼

answered Oct 20 '11 at 04:23 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x235
x13

asked: Oct 19 '11 at 10:49 AM

Seen: 13286 times

Last Updated: Oct 19 '11 at 10:49 AM