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.
asked Oct 19 '11 at 10:49 AM in Default
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
answered Oct 19 '11 at 11:07 AM
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:
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:
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.
answered Oct 20 '11 at 04:23 PM