Post reply

What happens to SQL Server when we shut down/Start Windows..?

  • July 2, 2011 at 11:49 pm

    #240851

    Expertrs,

    I may sound stupid..

    1. What happens to SQL Server when we shut down/Start Windows..?

    Sometimes SQL server takes hell lot of time after starting windows... (Restoing something...). What's the best way to shutdown sql server.?

    2. How can we know if SQL Server is busy at a particular point of time.?

    Thanks.

    Smith

  • July 3, 2011 at 12:26 am

    #1347516

    •When windows server gets stops and starts at the same time MS Sql server related services stops and starts correspondingly.

    •And we check with below query that why sql server is busy at particular point.

    sp_who2 active

    Regards,

    Piu : http://bit.ly/jnuU1R

    Regards,
    Piu : http://bit.ly/jnuU1R

  • July 3, 2011 at 3:35 am

    #1347524

    Joy Smith San (7/2/2011)

    1. What happens to SQL Server when we shut down/Start Windows..?

    It stops and restarts. Nothing fancier than that.

    Sometimes SQL server takes hell lot of time after starting windows... (Restoing something...). What's the best way to shutdown sql server.?

    Restoring or recovering? Two very different things. I'd expect to see recovering after a server restart, I would not expect to see restoring

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 3, 2011 at 9:34 am

    #1347549

    It is possible that it is taking a long time to recover because you have a large transaction log - with a lot of active VLF's.

    How large is your transaction log for each database on that server?

    What are the recovery models for each database?

    For all databases in full or bulk_logged recovery model, are you backing up the transaction log frequently?

    That is where I would start looking.

    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

  • July 4, 2011 at 12:53 am

    #1347640

    Jeffrey Williams-493691 (7/3/2011)

    It is possible that it is taking a long time to recover because you have a large transaction log - with a lot of active VLF's.

    How large is your transaction log for each database on that server?

    What are the recovery models for each database?

    For all databases in full or bulk_logged recovery model, are you backing up the transaction log frequently?

    That is where I would start looking.

    Thanks.

    DB is 200 GB. Mirroring is enabled on that, Hence Full recovery.

    Thanks.

  • July 4, 2011 at 12:57 am

    #1347641

    It stops and restarts. Nothing fancier than that.

    Restoring or recovering? Two very different things. I'd expect to see recovering after a server restart, I would not expect to see restoring

    Thanks Gila.

    Sorry my question was not clear. Actualy I wanted to ask, many users were connected and open transactions were there.? Or someother process like backup was on .?

    Yes, It was recovering...

    Thanks again.

  • July 4, 2011 at 1:13 am

    #1347646

    Lots of open transactions or committed transactions not written to disk. That, especially if you have lots of VLFs will cause a long recovery time.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 4, 2011 at 9:00 am

    #1347916

    and don't be tempted to hold the power button for 4 seconds neither, it'll take just as long otherwise when you restart 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 4, 2011 at 11:43 pm

    #1348059

    Perry Whittle (7/4/2011)

    and don't be tempted to hold the power button for 4 seconds neither, it'll take just as long otherwise when you restart 😉

    4s! Thats to slow... much faster just to yank the powercable. 😀

    /T

  • July 5, 2011 at 12:08 am

    #1348065

    A faster shutdown, but a sloooooooooow startup 🙂

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 5, 2011 at 12:20 am

    #1348068

    So ultimately it takes long time while restarting because of "open transactions or committed transactions not written to disk"... Am I right.?

    Thanks to all.

  • July 5, 2011 at 2:35 am

    #1348103

    Yup. It's called restart recovery. It'll be slower than necessary if you have excessive VLFs. There are several posts on Kimberly Tripp's blog on that (google to find them, I don't have links handy)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 5, 2011 at 3:34 am

    #1348136

    Thanks Gila.

    Am pretty much clear now.

  • July 5, 2011 at 3:38 am

    #1348138

    using checkpoint periodically could help ease the pain

    http://msdn.microsoft.com/en-us/library/ms188748.aspx

    Jayanth Kurup[/url]

  • July 5, 2011 at 3:41 am

    #1348142

    Checkpoint runs periodically anyway.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply