October 28, 2014 at 2:05 pm
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?
Experts need your valuable thoughts.
Thanks a ton
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 28, 2014 at 4:27 pm
Sapen (10/28/2014)
Also are there any specific causes that will take a database into suspect mode?
SQL encounters a corrupt or unreadable page while doing a transaction rollback or crash recovery. Details will be in the error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2014 at 12:51 am
This was removed by the editor as SPAM
October 29, 2014 at 1:05 am
jacksonandrew321 (10/29/2014)
improper shutdown of the sql server services,
Not unless the files are damaged in the process. SQL is perfectly capable of recovering from unexpected shutdowns
not enough disk space when writing to the database,
No, that just results in the transaction trying to write failing and rolling back.
sql server can’t access log file data while in online mode (because of antivirus programs),
Only time an antivirus could do that is before SQL opens the database (before it gets a lock on the file). If that happens, the database goes RECOVERY_PENDING, not SUSPECT. If the log file disappears while SQL is running (drive failure), the DB goes into a read-only state, not suspect.
sql server goes down to shutdown in the middle of transaction which results in log file corruption etc.
Well that won't cause log file corruption, SQL is perfectly capable of shutting down in the middle of transactions and recovering safely
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply