Tag Archives: locks

Changes to the state or options of database cannot be made at this time

One of our production servers running SQL Server 2005 has LiteSpeed log shipping configured. It is configured to bring the database into Single user mode, apply the transaction log backups from Primary server and put the database back into Multi_User mode. Due to some configuration changes at the Primary database, the logshipping job at the secondary instance failed. This left the Secondary database in Single User mode.

In order to resolve the issue the DBA had to bring back the database into Multi_User mode and apply the transaction log backups manually. Like I discussed earlier in this post, DBA issued the ALTER DATABSE command with termination option to bring the database into Multi_User mode.

ALTER DATABASE databasename SET MULTI_USER WITH ROLLBACK IMMEDIATE

But this query returned error which read

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'databasename' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed

Error message clearly stated that the database cannot be brought into Multi_User mhode since it is in Single User mode and currently one user is already connected to it. This is because a user having similar privileges as that of the one issuing the Alter Database command was already connected to the database. Hence the Rollback Immediate option will not terminate that connection.

The DBA immediate checked if he has connected to that database already. He was not using that database anywhere. To check which process was using that database, the DBA issued the following commands.

select * from sys.sysprocesses WHERE dbid = 5

SELECT * FROM sys.dm_exec_requests WHERE database_id = 5

Both these queries returned no results. Now this became more interesting, no one is using this database, still SQL Server reports that the database has an open transaction. Then the DBA decided to run sys.dm_tran_locks DMV which reports information about currently active lock manager resources on the instance.

select * from sys.dm_tran_locks where resource_database_id=5

This DMV did the trick and reported the locks currently held on that database.

The request_session_id column in the output reported that SPID 55 was holding these locks on the database. This information can also be obtained from the Activity Monitor in SSMS. SPID 55 was accessing this database through a nested query.

Now that the DBA has identified which process was active on this database, all he needed to do was to kill this process so that the database can be brought into Multi User mode. After confirming with the Application Team, this process was killed and the database was brought into Multi User mode and the transaction log backups were applied manually.

Process ID owns resources that are blocking processes on Scheduler

A few days back one of the regular readers of my blog $sammy (not sure how he wants to be called, I will call Dollar Sammy ;-)) saw continuous  error messages on one of his SQL Server 2000 instances which read

Process ID 61:924 owns resources that are blocking processes on Scheduler 2

This messages is one of the newly introduced features in SQL Server 2000 SP3. This indicates problem with the Scheduler. This article on MS site explains it better.

To find out the root cause, I checked the following.

  • Whether SPID 61 was causing any blockings on the other transactions. No, it was not
  • There were no parallel queries being executed at the time of this error message.

Then went on to check the locks held by this process. This process was holding a whopping 312 locks on different objects on that database! The pattern that I observed was that 25 worker threads were waiting for one worker thread to release the lock. There were 12 such locks held on different objects (25 + 1) * 12. The locks were acquired as soon as this query was fired.

Obviously these worker threads had put pressure on the Logical Schedulers. Once the query got executed successfully, this error message ceased to appear in the error log. I am yet to look into the query which in question, for sure it requires some tuning.

$sammy, I hope this answers your question !