Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008

    Unanswered: OS Shutdown with running MSSQL

    I have my suspicions, but I need some proof ...

    Is restarting a Windows OS box without first stopping the MSSQL service comparable to a shutdown with nowait command? I know that MSSQL will recover, but wouldn't it recover more nicely if it was shutdown on its own terms prior to the box shutting down? OR. Since it's a Windows service does the OS *know* to stop the service gently, allow the engineto place checkpoints on every db and then shut down the OS?

    I've googled plenty and cannot find an exact answer. Lots say it's fine to do - doesn't mean it's right


  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    it's all about the transaction log and the two phase commitment of transactions and recovery. everything is taken care of nice and neatly but occasionally you will get a torn page and your DB will go suspect and that is why a good backup strategy is necessary.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2008
    Torn pages is a good argument why not to shutdown a machine without first stopping mssql services.

    I need more though Our IT folks are rebooting all the Windows servers weekly as part of the maintenance plan (we shall not speak of this part). It feels dirty & wrong to me to just shut down the OS without first pausing and then stopping MSSQL.

    I want to ensure we're not losing transactions. It seems to be that if the db cannot checkpoint, it has the potential to rollback when the engine starts back up.

    We do have good backup & recovery in place. I prefer, however, to keep it as a fire drill and not an actual fire.


  4. #4
    Join Date
    Jan 2003
    Provided Answers: 11
    Torn pages are a rarity, so long as you have the torn page detection turned on.

    Also, remember the transaction log is written to synchronously, so the record is hardened in the transaction log immediately after the end of the transaction, whether it is implicit or explicit. Only user transactions that are taking place exactly at the time of shutdown will be rolled back. If the IT folks are rebooting all the machines, one would hope it is at least a quiet time of the week, and not noon on Wednesday.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts