Could not obtain exclusive lock on ‘model’ and Appassure


The was an interesting query from a client. A third party SQL based app had been failing for a few weeks when it was initialising its mssql database with new data. this had been running successfully for some time but had stopped without explanation. Ultimately the error “Could not obtain exclusive lock on ‘model’ ” was given as the reason why the application was failing. My first instinct was to check the database to see what application might have a lock on the model database as it was being used as a template for the third party app to create and populate its database separate from Model.

using

 

Use master

GO

IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID(‘Model’))

PRINT ‘Model Database being used by some other session’

ELSE

PRINT ‘Model Database not used by other session’

actually  gave the response “model database not used by other session”!!

And the query

SELECT request_session_id FROM sys.dm_tran_locks

WHERE resource_database_id = DB_ID(‘Model’

therefore gave the same result – no rows – so no process being identified.

The next stroke of luck was realising that one thing about the machine and database in question was that the SQL Db was being protected by AppAssure – a backup and restore solution from Dell which runs an agent to provide the backup snapshots. Shutting down the agent temporarily allowed me to create and more importantly delete new databases. This lead me to research issues regarding Appassure and the error to find this article which in summary says

 

Error: “Could not obtain exclusive lock on database”. Retry the operation later. CREATE DATABASE (119742)

Title  – Error: “Could not obtain exclusive lock on database”. Retry the operation later. CREATE DATABASE

Description – Error:  “Could not obtain exclusive lock on database ‘model’. Retry the operation later. CREATE DATABASE failed.”

In some instances when AppAssure is protecting a SQL server, the agent service will stop custom scripted actions that create new databases from completing successfully.

Cause

Software defect.

Resolution

WORKAROUND 1:

On the agent server that is experiencing the issue, please do the following to disable SQL metadata gathering:

1. Open the registry editor (regedit.exe)

2. Browse to  HKEY_LOCAL_MACHINE\SOFTWARE\AppRecovery\Agent\AgentSettings\DisableSqlMetadata

3. If the value exists set it to a value of 1

4. If the value does not exist, create the DWORD key and then set the value to 1

5. Restart the agent service

Once completed this will disable all SQL metadata gathering and will stop AppAssure from locking the model DB.  Please note that without SQL metadata gathering, SQL attachability checks within AppAssure are not possible.

WORKAROUND 2:

A patch has been released to resolve this issue. Before requesting the patch please upgrade the agent to the latest version available from the license portal.

Please contact support for the latest patch to resolve this issue.
LINK TO DETERMINE WHAT PATCHES ARE INSTALLED:  
https://support.software.dell.com/appassure/kb/132353
VIDEO INSTALL PATCH LINK:  
https://support.software.dell.com/appassure/kb/126791

STATUS:

This issue will be resolved in a future release of AppAssure
The current version of AppAssure may be downloaded here:  https://support.software.dell.com/appassure/download-new-releases

)