July 5, 2011 at 3:52 am
But setting the recovery interval would make sure it runs frequently enough to make sure the recovery doesn't exceed the time limit specified.
Also, i wasn't able to find anything that says checkpoint is a scheduled operation , msdn says its event based.
Another thing , SQL wont restart automatically after an OS shutdown unless the services explicitly states automatic as the startup 😀
July 5, 2011 at 4:14 am
Jayanth_Kurup (7/5/2011)
Also, i wasn't able to find anything that says checkpoint is a scheduled operation , msdn says its event based.
It's not scheduled. It runs when SQL decides to run it, based on the recovery interval and the amount of activity.
Another thing , SQL wont restart automatically after an OS shutdown unless the services explicitly states automatic as the startup 😀
Which is the default. And a manual startup on a production server would be a very silly setting (unless clustered, in which case cluster admin auto-starts the service)
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
July 5, 2011 at 4:17 am
Jayanth_Kurup (7/5/2011)
But setting the recovery interval would make sure it runs frequently enough to make sure the recovery doesn't exceed the time limit specified.Also, i wasn't able to find anything that says checkpoint is a scheduled operation , msdn says its event based.
True there are events that drive it but there is a default. This link contains details. A snippet from Books Online below
Books Online
The default is 0, indicating automatic configuration by SQL Server. In practice, this means a recovery time of less than one minute and a checkpoint approximately every one minute for active databases.
Jayanth_Kurup (7/5/2011)
Another thing , SQL wont restart automatically after an OS shutdown unless the services explicitly states automatic as the startup 😀
as far i remember no one is talking about auto startup. Whether auto or manual, it will still complete the same startup operations for the service\instance!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 5, 2011 at 5:13 am
July 5, 2011 at 5:33 am
Long running uncommitted transaction is the typical reason.
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
July 5, 2011 at 5:37 am
on a high traffic database how many transactions could take place in the space of a minute or even five minutes??
Have you ever wondered why when you shut down SQL Server the HDD lights are flashing like a christmas tree?
Pulling the plug or switching off without allowing SQL server to flush to disk will cause the startup to be slow in most cases as the log has to be replayed and committed to the disk before the database can come online. The database has to start from a consistent state!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 5, 2011 at 5:48 am
SQL will try to checkpoint all databases before shutdown, but it usually won't have time if windows is shutting down.
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
July 5, 2011 at 5:51 am
GilaMonster (7/5/2011)
but it usually won't have time if windows is shutting down.
😀 especially if some monkey pulls the plug or holds the power button in for 4 secs 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 6, 2011 at 12:20 am
GilaMonster (7/5/2011)
SQL will try to checkpoint all databases before shutdown, but it usually won't have time if windows is shutting down.
Just one more doubt... Will it be safe if I stop SQL server service and then shut down the windows.? Is it Ideal.?
Thanks.
July 6, 2011 at 1:01 am
Sure. Completely safe. So is just shutting down windows without first stopping SQL. SQL will recover, it guarantees that.
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
July 6, 2011 at 3:41 am
July 6, 2011 at 4:19 am
Thank you all. Learned something new and important..
Thanks again.
July 6, 2011 at 6:45 am
Jayanth_Kurup (7/6/2011)
Moral of the Story :-before shutting down the OS give SQL enough time to perform a clean shutdown of the databases else face long recovery times when things come back online.
May face long recovery times. It could be fine with an immediate stop. It could take ages to recover even if SQL was allowed a clean shutdown.
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
July 6, 2011 at 1:11 pm
July 6, 2011 at 1:14 pm
I've already said it at least twice in this thread.
Long running uncommitted transactions.
Depending how long they've been running such a transaction could take hours or longer to roll back. It has to roll back sometime, and I don't think a SQL shutdown, even a controlled one, will wait that long. Even if it does, the DB is still unavailable, you'll get 'cannot connect as SQL is shutting down' type errors.
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 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply