February 8, 2011 at 4:51 am
Shutdown shuts the SQL instance down, not a database. It's possible to detach a database and not have it cleanly shut down, primary example if the log is full.
Clean shut down means that SQL managed to roll back all uncommitted transactions, write all changed data pages to disk. It means that there will be nothing for recovery to do upon restart. It's the only time that the log can be deleted without leaving the DB recovery_pending.
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
February 8, 2011 at 5:11 am
GilaMonster (2/8/2011)
Shutdown shuts the SQL instance down, not a database. It's possible to detach a database and not have it cleanly shut down, primary example if the log is full.Clean shut down means that SQL managed to roll back all uncommitted transactions, write all changed data pages to disk. It means that there will be nothing for recovery to do upon restart. It's the only time that the log can be deleted without leaving the DB recovery_pending.
Gail, thanks for clarifying. That means we can never be sure if we could bring the db back online if we loose the log file after a detach operation. Only upon performing an attach, we would know if a db comes back online based on the success\failure of attach operation.
M&M
February 8, 2011 at 5:15 am
mohammed moinudheen (2/8/2011)
GilaMonster (2/8/2011)
Shutdown shuts the SQL instance down, not a database. It's possible to detach a database and not have it cleanly shut down, primary example if the log is full.Clean shut down means that SQL managed to roll back all uncommitted transactions, write all changed data pages to disk. It means that there will be nothing for recovery to do upon restart. It's the only time that the log can be deleted without leaving the DB recovery_pending.
Gail, thanks for clarifying. That means we can never be sure if we could bring the db back online if we loose the log file after a detach operation. Only upon performing an attach, we would know if a db comes back online based on the success\failure of attach operation.
Sorry for my last line.
Only upon performing an attach, we would know if a db comes back online based on the success\failure of attach operation.
Just wanted to say,
Only upon performing an attach, we would know if a db comes back online based on whether the db happened to be shut down cleanly or not.
M&M
February 8, 2011 at 5:36 am
mohammed moinudheen (2/8/2011)
That means we can never be sure if we could bring the db back online if we loose the log file after a detach operation.
Simple, don't lose the log file. Consider it as essential as any of the data files. You wouldn't think about maybe losing one of the ndf files, treat the log the same way.
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
February 8, 2011 at 6:53 am
Thank you Gail
M&M
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply