Could not obtain exclusive lock on database 'MODEL'. Retry the operation later.

Question

Could not obtain exclusive lock on database 'MODEL'. Retry the operation later.

Ant-Green

SSC Guru

Points: 113507
More actions
December 1, 2008 at 3:59 am

#198241

Guys and Gal's
We have installed SQL 2000 a few months back now on a Virtual Server and all was working fine.
We moved the master, msdb, model, tempdb databases to be on seperate drives for logs and data and all was working good.
Come today to look at the properties of a couple of the databases and it is saying that the database Model was not found in the collection.
I have checked Enterprise Manager and the model database isnt there, I have re-attached the DB and I am getting the error message Could not obtain exclusive lock on database 'MODEL'. Retry the operation later.
Stopping the SQL Agent allows me to attach the database, but it does not show in a refresh on EM or by select * from sysdatabases.
Any ideas guys and gals
Ant

Viewing 9 posts - 1 through 8 (of 8 total)

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

Vee Hall of Fame Points: 3028 More actions · Answer 1

Hi ,

Can you please set the default locations for the databases on the server properties and restart the server and check if all the databases are coming up.

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

database default locations where changed at the time of installation.

D for data

F for logs

G for backups

we have been stopping and starting the SQL services through out the day and all databases come back online everytime

Vee Hall of Fame Points: 3028 More actions · Answer 3

In that case, i believe something has gone wrong. Can you please check the eventvwr, errorlogs to check if anything fishy happened.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

thats the thing, there are no errors in the event log of sql log, i am totally baffled to this

Vee Hall of Fame Points: 3028 More actions · Answer 5

Can you please see the old sql server errorlog when the model database last worked. Is the server is now up and running ?. or is there any outage?. can you please check in the sysaltfiles to if any file listing found for model. what i can suggest here is. If your master database is not curropted then see if you can get ldf and mdf files for the model from the simillar test server where collation setting are same. Place them in the location where you can see the old mdf and ldf (propably you can rename them as old and try restarting the server.)

Adiga One Orange Chip Points: 27284 More actions · Answer 6

Did the issue got resolved? Even I faced the same issue today and found that one SPID was blocking the Database Creation SPID. This resulted in the failure of DB Creation.

I restarted the SQL Services and was able to create the database.

The blocking SPID was executing something like this

Language Event 0

declare @RegPathParams sysname

declare @arg sysname

declare @Param sysname

declare @MasterPath nvarchar(512)

declare @LogPath nvarchar(512)

declare @ErrorLogPath nvarchar(512)

declare @n int

select @n=0

sel

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

Hi Adiga

No our issue hasnt been resolved as it will require a full rebuild of the virtual server and SQL.

As the server is SQL 2000 we move MSDB and MODEL onto their own drives using SP_DETACH_DB.

From what I believe SQL looks to put MODEL as DBID 3 in SYSDATABASES but as MSDB was attached before MODEL, MSDB took DBID 3 and MODEL wouldnt reattach,

The artical we use is http://www.databasejournal.com/features/mssql/article.php/3379901/Moving-the-Tempdb-and-Master-Database-in-SQL-Server.htm which says to put -T3608 trace flag and then detach the DB's, move then attach the DB's

This is not so much an issue for us now as we have manually attached a copy of the db we where trying to create from another instance of SQL.

Thanks

Ant

EdVassie SSC Guru Points: 60472 More actions · Answer 8

If MSDB has Id 3 then you need to detach MSDB then attach MODEL as DB id 3. In order to detach MSDB you will need to stop SQL Agent.

If the system databases do not have the following dbid values than SQL Server will not work correctly...

master 1

tempdb 2

model 3

msdb 4

The best practice today is do not move master and model databases from their default locations. There are no performance or data integrity reasons for moving these databases, but moving them can cause you problems.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara