January 16, 2019 at 1:42 pm
One of our database is stuck in recovery for a very long time. The database got corrupted and after a failover, its unable to recover. I tried a few things and its now in single user mode and wont come out of it. Also, its holding exclusive locks on this database and i am unable to see those session id's in sp_who2
When i query sys.dm_tran_locks, it has the below output.
| resource_type | request_mode | request_type | request_session_id |
| DATABASE | S | LOCK | 89 |
| DATABASE | X | LOCK | 89 |
| DATABASE | S | LOCK | 7303 |
| PAGE | IX | LOCK | 89 |
| OBJECT | X | LOCK | 89 |
| OBJECT | IX | LOCK | 89 |
| KEY | X | LOCK | 89 |
Also, when i query dbccinputbuffer(89) it gives no result
| EventType | Parameters | EventInfo |
| No Event | 0 | NULL |
Any idea how i can resolve this?
January 16, 2019 at 2:21 pm
try that.
1) shutdown sql service
2) copy mdf/ldf files somewhere so that you can come back to this point if required
3) start sql server service
run the following. (change database name in the statements)
use master
go
ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
-- Now try again...USE [DemoSuspect];GO -- Check the stateSELECT DATABASEPROPERTYEX (N'DemoSuspect', N'STATUS') AS N'Status';GO
January 16, 2019 at 2:32 pm
I have restarted the sql service multiple times with no success. Moving the files is not an option as there are multiple databases on that instance and stopping the service for that long will not be an option.
Also, I have executed the commands several times and i get the 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."
January 16, 2019 at 2:34 pm
goher2000 - Wednesday, January 16, 2019 2:21 PMtry that.1) shutdown sql service
2) copy mdf/ldf files somewhere so that you can come back to this point if required
3) start sql server servicerun the following. (change database name in the statements)
use master
go
ALTER DATABASE [DemoSuspect] SET EMERGENCY;
GO
ALTER DATABASE [DemoSuspect] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
-- Now try again...USE[DemoSuspect];GO-- Check the stateSELECTDATABASEPROPERTYEX(N'DemoSuspect',N'STATUS')ASN'Status';GO
Don't do this. While it may get your database back online, the amount of data you could lose may have you searching for a new position.
A few questions.
1. Are restores from backups possible?
2. This was caused by a failover. What KIND of failover? A cluster, An availability group? Something else? If it's an AG or cluster, did all of the cluster resources come back online? Has cluster manager provided you with any details in the logs?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 16, 2019 at 2:37 pm
SQLNewbie_01 - Wednesday, January 16, 2019 2:32 PMI have restarted the sql service multiple times with no success. Moving the files is not an option as there are multiple databases on that instance and stopping the service for that long will not be an option.
Also, I have executed the commands several times and i get the 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."
HOW have you re-started the service? Service manager? You need to use SQL Configuration manager. You probably should be using Failover cluster manager.
Have the other databases came back online successfully?
Try this command:
ALTER DATABASE <database> SET MULTI_USER WITH ROLLBACK IMMEDIATE
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 16, 2019 at 2:55 pm
I initiated the failover through failover cluster manager to restart the services. All other databases are online except this one. I have tried executing various commands including this one but no success
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [DBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
It gives me the 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.". Any inputs?
January 16, 2019 at 3:12 pm
SQLNewbie_01 - Wednesday, January 16, 2019 2:55 PMI initiated the failover through failover cluster manager to restart the services. All other databases are online except this one. I have tried executing various commands including this one but no successSET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [DBName] SET MULTI_USER WITH ROLLBACK IMMEDIATEIt gives me the 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.". Any inputs?
You need to identify the process that is connected to that database. Once you have found that connection - you can then determine whether or not you want (or can) terminate that session. Until that connection is no longer connected to that database you cannot bring the database back to multi-user.
If you can kill the session connected to that database - once it has been terminated you can then issue the statement to set it back to multi-user.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 16, 2019 at 5:18 pm
Run this to try to find the connected spid.
SELECT db_name(dbid), *
FROM sys.sysprocesses
WHERE db_name(dbid) = 'Your database Name'
You can attempt to issue the KILL command on the offending spid.
How big is this database? It may simply be taking time to come back online.
There are a couple issues and questions that you need to consider.
If there is an application that is running, and trying to connect, it may re-connect as soon as you kill the spid. You may never get the chance to run the command to set it to multi-user.
What was the reason that you failed it over? I suspect it may not have been a very good reason! If there was a large transaction or a rollback that needs to occur, you may be simply waiting for this to happen.
Some random things to try:
Fail it back. I have seen instances as well as individual databases fail to come online when there are differences in the nodes of the clusters.
Re-boot the servers. Fail it back and forth if needed.
Stop the SQL instance. Copy and rename the .mdf and .ldf files to something different.
Move or rename the existing files so that the database does not try to bring it online when SQL is re-started.
Re-start SQL.
Issue this command. I think the syntax is correct.
CREATE DATABASE MyDatabase
ON(NAME='Logical Data File Name',
FILENAME='C:\yourpath\renamed.mdf')
LOG ON(NAME='Logical Log File Name',
FILENAME='C:\your path\renamed_log.ldf')
FOR ATTACH
If the database comes online, you should be able to set it to multi-user, and run DBCC CHECKDB. See what errors come up.
If it comes back clean, back it up and restore it as the original database.
If it doesn't, open a support ticket with MS, or restore the the last known good backup (You have one of those, correct???), or keep posting!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply