The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. (Microsoft SQL Server, Error: 1204)

This error has made another day of my typical DBA life. We have a restore job which takes backup of production DB and restores it to test server. When restore to test server was happening, the test server was rebooted by windows team for finishing some critical windows patch deployment. Server came up fine after reboot, I have verified and noticed the DB was also ‘Online’, which was in mid of restore prior to reboot. But I started receiving complaints from users that applications are not accessible.

when I tried to open the database through SSMS I received below error:

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. (Microsoft SQL Server, Error: 1204)

Below are the options I tried to fix the issue, I’m listing out everything I tried so that anyone of the options below might help someone in fixing their issue:

I verified applications relying on the DB and they are throwing error related to permission issue. So I tried granting permission to the users for which its giving permission issue but the grant permission commands never completed and has thrown same above error.

I ran sp_who2 to find out what are the active SPID’s currently in the server and found no user SPID’s for this DB, but there was a SPID 20 which was a Background process and it was running the command ‘DB STARTUP’ on the same DB which was having issue, the CPUTime and I/O is quite high for this SPID. So this confirmed that the DB STARTUP command got stuck due to the reboot and not released.

As it was a BackGround SPID we cannot KILL it... DB was showing ONLINE through SSMS and even sys.databases table also shows DB is ONLINE, but data was not accessible… I tried running the restore command manually with RESTART option but it didn’t helped. 

Note: 'RESTART' option in restore command can be used when we have a failure while restore is happening and if we want to start the restore from same point where restore failed.

Eg: Restore database dbname from disk = ‘C:\temp\test.bak’ with RESTART;

Okie I tried brining the DB to SINGLE USER mode with below command but it has thrown error ‘Database is in a state where you cannot put it in SINGLE USER mode’

ALTER DATABASE dbname

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

Tried taking the DB OFFLINE but still that didn’t worked:

ALTER DATABASE dbname  SET OFFLINE WITH ROLLBACK IMMEDIATE;

Tried dropping the DB, nope that didn’t worked too… for the above trails that is for dropping and taking DB offline, it was throwing error ‘Cannot perform this operation as database is in use’. When verified the same SPID 20 which was running the command ‘DB STARTUP’ was blocking these commands.

Now when I ran sp_who2 again it started throwing below error for query as well:

Msg 1204, Level 19, State 4, Procedure sp_who2, Line 89

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

When I refreshed the GUI, I got a pop-up window showing same error, now SQL Agent also not visible in SSMS. So I decided to restart the SQL services, I took all SQL related services down and started them back, I noticed now the DB was in ‘In Recovery’ state so I was happy it started recovering, when checked in SQL logs its showing the recovery status of the DB 20%,60%,90% and 100% completed. After 100% there was an entry in log stating DB is online. Hurray!!! DB is back…. Nope not so easy when I tried accessing the DB same above errors L

Okie I tried restore command with ‘recovery’ option, nope this didn’t worked tooo…. it has thrown same error ‘Database is in a state where you cannot put it in SINGLE USER mode’

Eg: restore database dbname with recovery

Note: This recovery option will bring the DB online if the DB was left in ‘In Restoring’ state.

Now I started searching in google and almost all the articles(at least what I found) says they received this error while running a SP or bulk query or during any heavy transaction, but none of those were my case… NOW WHAT!!!

As a last option in order to get rid of the DB, I took SQL services down and renamed its corresponding mdf/ldf files and started the services. In normal case DB will go to SUSPECT state when the underlying mdf/ldf files are not available but NO in this case it didn’t happened, even without mdf/ldf files also the DB status was showing as ONLINE through GUI and in sys.databases table as well(J it’s a miracle)…. Anyways now at least the stuck SPID 20 no longer exists and now it allowed me to drop the DB and after dropping it I deleted the mdf/ldf files of it and started the restore job again. Once restore job completed successfully the DB was really ONLINE and was accessible!!!

The resolution I used is the last option, as mine was a test server and I have a restore job which can bring back the DB, so I did that and I DO NOT RECOMMEND this to anyone. 

If anyone faced same kind of issue and have fixed it without dropping the DB please share the details.

Thanks!!