Question

oleolehoohoo on Tue, 28 Oct 2014 20:04:08


I am using SQL 2012 SE with A-P clustering. We had an automatic failover around 3:00AM and the replication publisher database went into suspect mode. I gave it some time to see if it would come back to normal mode all by itself and when it did not I took the db to emergency mode and then single user mode and did dbcc check and then when there were no errors I tried to bring it to multi user mode and it would still remain in emergency mode.

I tried to do take it offline and bring it online.But it wont go offline as it says database is in replication and cannot be taken offline. So I had to broke replication and take the database offline and brought it back online and then restarted the replication from scratch.

I came to know that the LUN went offline and then came back online and that triggered the failover. 

But, why did the publisher database go to suspect mode? I told my IT manager that the underlying disk that went offline was hosting the publisher database and since the disk went down and came back it took the database to suspect mode but he would believe it. Is there a way that we can tell for sure that a specific issue caused the database go to suspect mode? Like any log/eventvwr? I had transaction log backups running like once every 10 mins for publisher database...could that be a reason?

Also are there any specific causes that will take a database into suspect mode?

Thanks a ton


Sponsored



Replies

Olaf Helper on Wed, 29 Oct 2014 06:52:14


Is there a way that we can tell for sure that a specific issue caused the database go to suspect mode? Like any log/eventvwr?

Hello,

Yes, you can check the SQL Server ErrorLog (in SSMS) and Windows EventLog (EventVwr.msc) for details about what have caused the issue.

Uri Dimant on Wed, 29 Oct 2014 08:21:09


From my experience  most of the problems because of physical disk failure ... look also into Event Viewer.

Ashwin Menon on Wed, 29 Oct 2014 09:18:42


While in emergency mode you should try to make the database Online by running the command

alter database dbname set online

Putting into multiuser mode will not change it from emergency mode.

The reason for your database going suspect would be due to the LUN issue which can be made clear by looking at the SQL Error log and confirming it with eventviewer whether the LUN failures occurred during the same time.

Kalman Toth on Wed, 29 Oct 2014 19:01:15


Blog: http://www.sqlusa.com/bestpractices2005/markedsuspect/

RAJU RG on Wed, 29 Oct 2014 20:22:03


Replication --- Is this sql standard replication or AlwaysON ? my understanding is you have Active - Passive cluster with sql standard replication or AlwaysON ??

Anyway you have to view all the logs for the suspect reason and then you have to fix that database...

http://www.sql-server-performance.com/2012/recovery-sql-server-suspect-mode/

Tom Phillips on Wed, 29 Oct 2014 20:30:52


The SQL Server log file will tell you something like:

Unable to access S:\data\file.mdf -- Setting database to suspect.

This is normal and expected when the LUN goes offline containing SQL Server files.  I have seen this many times when I had a bad fiber switch.

Lydia Zhang on Tue, 11 Nov 2014 07:26:43


Hi SQLSPUser,

I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps?  If it was resolved,  please help to close this thread. If not, please help to collect log information for analysis.


Thanks,
Lydia Zhang

oleolehoohoo on Tue, 11 Nov 2014 14:22:45


I was checking in the sql server logs and eventvwr and unfortunately couldnot find an entry for why the database went to suspect mode.