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

  • 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

  • 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.

  • 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

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

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

  • 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.)

  • 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

  • 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

  • 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

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

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