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:
- Stop sql services
- Rename physical file of db but don’t delete
- Restart SQL services which make database go suspect
- Take database in emergency mode
- Rename physical files back
- Take database online / Multi user mode ,if database doesn't come online then goto step 10
- Restore database backup available without taking in single user mode and with no recovery
- If again database go in offline , you can manually make it online
- Sync it with regular log shipping
- Step 1, 2 , 3
- Drop existing database
- Manually drop renamed physical files
- Restore database with available backups
- Sync same with log shipping
And finally my log shipping start working
No comments:
Post a Comment