March 3, 2009 at 5:46 am
Been doing some background reading on what if scenarios such as having to recover a suspect db.
seems it can be caused a lot by a database that was not cleanly shutdown. This raises two questions:
1. why would i have to shut down a database? ive often restarted the sqlsvr service with no issues. ive also detatched and reattached databases without doing a shutdown. what calls for a shutdown?
2. how exactly do you perform a shutdown of a database?
March 3, 2009 at 6:00 am
winston Smith (3/3/2009)
seems it can be caused a lot by a database that was not cleanly shutdown.
That's not enough to send a DB suspect. SQL can recover a database that wasn't cleanly shut down. It does that every time you start the service.
Starting up database 'Testing'.
1 transactions rolled forward in database 'Testing' (10).
0 transactions rolled back in database 'Testing' (10).
Recovery is writing a checkpoint in database 'Testing' (10).
Recovery is complete
What will cause a DB to become suspect is if the DB is not cleanly shut down and then the log file is either damaged or missing.
The log is the key to getting a database that was not cleanly shutdown back to a transactionally consistent state. If the log's missing, the database cannot be brought online and it's state will be either 'recovery pending' or 'suspect'
1. why would i have to shut down a database? ive often restarted the sqlsvr service with no issues. ive also detatched and reattached databases without doing a shutdown. what calls for a shutdown?
Both of those operations you've just described.
When SQL is shut down, it shuts all of its databases down and then terminates the service. When you detach a database, SQL cleanly shuts it down and then removes any reference to the DB. A clean shutdown involved committing or rolling back all transactions, writing all dirty pages to disk and then writing an entry into the transaction log.
The only thing that will result in an 'unclean' shutdown is when the machine was shut down and there wasn't time to roll transactions back (eg a tran that would take an hour to roll back) because Windows only gives services a certain amount of time to finish up, when the SQL process is killed (through task manager) or when the server experiences an uncontrolled shutdown (power failure, overheating, drive failure)
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
March 3, 2009 at 6:15 am
Proper shutdown of a SQL Server is very much similar to a proper PC shutdown.
Where in the later case we close all application in use and make sure that no application is running and then go for a proper shutdown. Else the next time when the system is started, it would go for each and every disk check and proceed from there. It would not have any impact, however the booting time can be reduced.
Similarly, when a proper shutdown is given to a sql server, it would wait for the current transaction to complete, disable the logins, allow current sp's to run. Checkpoint each database and so. This would in turn minimize the recovery work that needs to be done by the sql server, when it is again restarted.
So, no much negative impact, but a best process to follow.
-Rajini
March 3, 2009 at 6:31 am
cool, so just to spell it out for myself, before restarting the physical server its always best practice to stop the sql service first so it has less recovery work to do, and also, to give it time to commit/rollback any transactions that are still open.
Is that correct ?
Thanks guys. much appreciated.
March 3, 2009 at 6:42 am
Not necessarily. Shutting down the machine will shut down the SQL service just as if you went and said 'stop service'
The only exception is if you have long running transactions that would be interrupted by a server shutdown, but in that case, you'd probably not be shutting down anyway.
Just don't kill the SQL process (Task manager - kill process) or do a hard shutdown of the server. But that's logical.
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
March 3, 2009 at 9:51 am
Even in the case of a power outage SQL Server most be able to recover unless disk corruption occurs!
* Noel
March 3, 2009 at 10:13 am
so what happens in the case of a power outage? is sql server able to recover without intervention, as long as the storage media is ok? there will be some uncommitted transactions in the tran log, but cant sql server just roll those back on startup?
March 3, 2009 at 10:19 am
winston Smith (3/3/2009)
so what happens in the case of a power outage? is sql server able to recover without intervention, as long as the storage media is ok?
Yes
there will be some uncommitted transactions in the tran log, but cant sql server just roll those back on startup?
Yes, which is exactly what it does.
The extract from the error log I posted above was from exactly that, a hard shutdown with uncommitted transactions.
All a hard shutdown means is that SQL will take longer to come online because it has to do restart-recovery on all of its databases.
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
March 3, 2009 at 10:53 am
winston Smith (3/3/2009)
so what happens in the case of a power outage? is sql server able to recover without intervention, as long as the storage media is ok? there will be some uncommitted transactions in the tran log, but cant sql server just roll those back on startup?
That is why I don't really think "Clean Shutdown" is something you can choose. SQL Server does recovery ALWAYS for you!
IF you don't want to miss transactions or rollback something then you should "quiesce" the DBs other than that you have "normally" no control.
* Noel
March 3, 2009 at 12:12 pm
noeld (3/3/2009)
That is why I don't really think "Clean Shutdown" is something you can choose.
It's not. SQL will always try to cleanly shut down all of its databases before it terminates.
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
March 3, 2009 at 12:56 pm
GilaMonster (3/3/2009)
noeld (3/3/2009)
That is why I don't really think "Clean Shutdown" is something you can choose.
It's not. SQL will always try to cleanly shut down all of its databases before it terminates.
Ermm ... Do I hear an echo ? 🙂
* Noel
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply