none
How to prevent SQL Server database becoming suspect on power failure?

    Question

  • Would like to know is there a way to overcome this?

    sql server does not rollback and its database becomes suspect after boot up on power failure

    Sunday, September 29, 2013 3:07 AM

Answers

  • Hi,

    As you expect this normally should not occur. SQL Server after unexpected shutdown during restart goes through recovery process for each database and does redo or undo of transactions based on if transactions are committed in transaction log or not.

    The most probable reason why your databases go to suspend status is some kind of corruption in transaction log. Maybe you are having problems with hard disk controller cache that is caching and not writing data to transaction log and loses this data in the event of power failure.


    HTH, Regards, Dean Savović

    Sunday, September 29, 2013 10:27 AM

All replies

  • Hi,

    As you expect this normally should not occur. SQL Server after unexpected shutdown during restart goes through recovery process for each database and does redo or undo of transactions based on if transactions are committed in transaction log or not.

    The most probable reason why your databases go to suspend status is some kind of corruption in transaction log. Maybe you are having problems with hard disk controller cache that is caching and not writing data to transaction log and loses this data in the event of power failure.


    HTH, Regards, Dean Savović

    Sunday, September 29, 2013 10:27 AM
  • Having a good backup strategy.... 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Shanky_621 Monday, September 30, 2013 7:40 AM
    Sunday, September 29, 2013 12:15 PM
    Answerer
  • sql server does not rollback and its database becomes suspect after boot up on power failure

    The SQL Server database engine can't really do much if the underlying hardware corrupts files due to a power failure.  The likelihood of this occurring is mitigated when the storage subsystem has a battery backup to protect write cache and avoid torn pages. 

    Be aware that a suspect database can be a temporary condition.  Sometimes files are unavailable during database startup but later become available.

    But at Uri suggested, you ultimately need a recovery plan to reduce potential data loss.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, September 29, 2013 12:40 PM
    Moderator
  • but it's not supposed to happen right?(db becomes suspect on power failure)

    but everytime I used TransactionScope, this ALWAYS happens

    I need to find the cause of it and prevent it from happening

    Please suggest ways to prevent this

    • Edited by z0802361 Sunday, September 29, 2013 2:31 PM
    Sunday, September 29, 2013 2:29 PM
  • First, read check the log to see what is the reason that rollback did not occured?

    exec sp_readerrorlog 0,1, 'err'
    exec sp_readerrorlog 0,1, 'fail'
    If there are no errors, make sure your database is not corrupted:
    DBCC CHECKDB('yourdbname') WITH NO_INFOMSGS, ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY, TABLERESULTS
    If you suspect that storage IO stack might introduce errors, you can check that with SQLIOSim. Run GUI version (adjust the path, this is default for sql2008r2):


    C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqliosim.EXE

    Configure several files on the disks similar to ones you are using for databases and let it run for at least one hour. If red lines (errors) appear, your storage (or path to the storage) generates corruption. Do that on the off-peak hours.

    If you suspect bug in a sql server generates corruption, check do you have the latest service pack and cumulative update.

    BUT, if a power failure is the trigger - io stack is not properly battery backed-up. Get at least UPS for the storage and switch you use to connect to the storage. That is the most likely cause of corruption.

    Sunday, September 29, 2013 3:58 PM
  • Disk caching is the most common cache. Disable disk caching.

    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, September 30, 2013 7:38 AM
    Moderator
  • In SQL error logs , it says MS DTC is the problem.

    So to use MS DTC or not to use it?

    If I use it, I solve my problem, but ended up with a Suspect SQL Database, and message queue failed to start up EVERYTIME I have a

    power failure.

    The error msg in the logs are:

    09/27/2013 19:24:43,spid54,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.

    Unknown,The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality<c/> please start this service.
    09/27/2013 19:24:39,spid60,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

    • Edited by z0802361 Monday, September 30, 2013 8:48 AM
    Monday, September 30, 2013 8:44 AM
  • Start MSDTC and recover msdb


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, September 30, 2013 8:55 AM
    Moderator
  • 09/27/2013 19:24:43,spid54,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.

    Unknown,The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality<c/> please start this service.
    09/27/2013 19:24:39,spid60,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

    Hello,

    Agree with Karthick, First message suggests recovery of transaction has completed ,second is itself saying please start MSDTC service.

    Now is this a clustered instance ,have you configured MSDTC properly? Please see below link for more details

    http://msdn.microsoft.com/en-us/library/aa561924(BTS.20).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Monday, September 30, 2013 9:38 AM
    Monday, September 30, 2013 9:30 AM
  • If I set the above settings, I will be unable to start Task Scheduler

    Task Scheduler will say I am not connected to remote.

    Monday, September 30, 2013 9:48 AM
  • 09/27/2013 19:24:43,spid54,Unknown,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.

    Unknown,The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality<c/> please start this service.
    09/27/2013 19:24:39,spid60,Unknown,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.

    Hello,

    Agree with Karthick, First message suggests recovery of transaction has completed ,second is itself saying please start MSDTC service.

    Now is this a clustered instance ,have you configured MSDTC properly? Please see below link for more details

    http://msdn.microsoft.com/en-us/library/aa561924(BTS.20).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    The logs above I believe is after I my db becomes suspect and I execute the above commands MANUALLY to make the db normal again:

    1.Use master GO ALTER DATABASE dbname SET EMERGENCY GO

    2.DBCC CHECKDB(db_name)

    3.ALTER DATABASE db_name set single_user with rollback immediate

    4. DBCC CHECKDB(db_name,REPAIR_ALLOW_DATA_LOSS)

    5. ALTER DATABASE db_name set multi_user

    I have given up on MS DTC

    These are the logs that were generated on failure:

    spid Unknown, Using 'xpstar.dll' version 2009.100.1600 to execute exteded stored procedure 'xp_enumerrorlogs' 

    spid ,Unknown, Attemping to load library 'xpstar.dll' intoo memory

    Logon, Unknown Login failed for user ' ' Reason:Failed to open the explicitly specified database 'db_name'

    Error:18456, Severity:14,State:38 (This is when db becomes suspect)


    • Edited by z0802361 Monday, September 30, 2013 3:09 PM
    Monday, September 30, 2013 3:06 PM
  • Please post the entire SQL Server error log.  The messages you posted are only informational, and are not the cause of suspect mode. 

    Monday, September 30, 2013 3:43 PM
    Moderator
  • Emergency mode and repair allow dara loss are last resort when hard corruption occurs. And yet, after repair you should run checkdb again to see if errors are gone.
    Monday, September 30, 2013 3:50 PM
  • without using ms dtc when i power down by plugging out the power cable, corruption does not happen

    it's obviously ms dtc's fault

    Tuesday, October 01, 2013 1:57 AM
  • somehow i need to start or restart ms dtc service on boot up

    even though i already set it to automatic

    • Edited by z0802361 Friday, October 04, 2013 10:29 AM
    Friday, October 04, 2013 8:40 AM