Database Is Offline With Single User Mode (Hidden Negative SPID)




Error: "Changes to the state or options of database  cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."

I have production server database involve in replication and log shipping. Log shipping failed due to some reasons on secondary and we decide to use full backup of database to restore in secondary.

So junior DBA took database in single user mode to release all connections and ran restore command,  but  unfortunately  after restore database went offline with below error. And now we are not able take database online


This does happen (database goes offline )  if we restore backup of publisher database and replication settings are not restored properly (Explained here).

But in my scenario database was offline with single user mode. 

And all below sql queries to take database online , multi user mode are failed with below error

  Alter database database set Multi_user with rollback immediate
  Alter database database set Multi_user
  Alter database database set Single_user with rollback immediate
  Alter database database set online
  Alter database database set online with rollback immediate

Failed with error:  "Changes to the state or options of database  cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."

In normal scenario we kill spid which holds connection on database (In single_user mode). But here database was offline and no spid was showing connected to database in sys.sysprocess \ sp_who.

So to find out SPID blocking I ran sql to take database in multi user mode in one query analyzer and in other  sql to find blocking and guess what i found SPID -2 is blocking .i.e  "Orphaned DTC Transaction

Click here to fix "SPID -2 an orphaned DTC transaction"

This seems easy but output of  below query  is
(Select req_transactionUOW from master..syslockinfo where req_spid = -2)















So there is no UOW exists in server for orphan DTC transaction.

You can also check all Orphaned DTC transaction using Query

SELECT
    [request_session_id] AS [SPID],
    [resource_type] AS [LockType],
    DB_NAME ([resource_database_id]) AS [DB],
    [resource_description] AS [Resource],
    [resource_associated_entity_id] AS [ResourceID],
    [request_mode] AS [Mode],
    [request_status] AS [Status]
FROM sys.dm_tran_locks
WHERE [request_session_id] < 0;
GO


















                               So now its confirm that my database have orphaned DTC connection having share lock due to single user mode and No UOW due to db offline.

To fix that i tried couple of more issues

1. Try to restore database: Restore database [dbname] from disk = backup
Failed with error: “Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.”

2. Detach database : EXEC master.dbo.sp_detach_db @dbname = N'database'
Failed with error:  "Changes to the state or options of database  cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."

3. Emergency Mode:  Alter database database Set emergency
Failed with error:  "Changes to the state or options of database  cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."

4. Rename physical data and log files manually
Failed with error:  "File is in use"

5. Drop database:   Drop Database [dbname]
Failed with error:  "Changes to the state or options of database  cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."


So now as last step of trouble shooting I tried to check local DTC in Component services (Check here)


















So no luck here also , None of transactions are visible here. So last option was to call MVP "Pradeep Adiga" and he suggest  to rename db physical files  or take database in emergency mode and if it doesn't work restarting sql can fix this 

So last option was to restart SQL Services following below steps

Steps to follow:

  1. Stop sql services  
  2. Rename physical file of db  but don’t delete
  3. Restart SQL services which make database go suspect
  4. Take database in emergency mode
  5. Rename physical files back
  6. Take database online / Multi user mode ,if database doesn't come online then goto step 10
  7. Restore database backup available  without taking in single user mode and with no recovery
  8. If again database go in offline , you can manually make it online
  9. Sync it with regular log shipping
  10. Step 1, 2 , 3 
  11. Drop existing database
  12. Manually drop renamed physical files
  13. Restore database  with available backups
  14. Sync same with log shipping

And finally my log shipping start working 

No comments:

Post a Comment