January 12, 2009 at 2:00 pm
I have a SQL 2005 DB (DB_1) that has a status of: "shutdown, normal".
autoclose is set to true, autoshrink is set to true.
these options are the same on another SQL 2005 DB (DB_2) but it has a status of "normal".
I notice that in the sys.databases view that is_cleanly_shutdown flag is set to 1 for DB_1 yet I can access it, could anybody explain what the is_cleanly_shutdown flag means?
thanks
January 12, 2009 at 2:11 pm
It means that the database has been closed and the when it was closed SQL was able to finish all transactions and write all dirty pages to disk. Hence no restart-recovery is necessary next time it's opened (which for a DB in autoclose will be the next time someone accesses it)
When a database has autoclose on, as soon as there are no connectins using it, SQL closes the database and shuts it down cleanly. The next time someone accesses it, the database is brought back online. This may take a second or two. With your databases, the first has no one accessing it, hence has a status of closed, the second has an active connection, so it's online. Once that connection closes, it'll go back to closed.
Why does the DB have autoclose enabled? It's not a good idea unless it's a database that's almost never used on an otherwise busy server. If it's frequently accessed, it means that SQL is frequently closing and opening the database.
Why, why, why is autoshrink on? Please read this as well as the two articles linked at the bottom of it - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-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
January 12, 2009 at 2:19 pm
Hi Gail,
so DB_2 (Status = normal) that has autoclose set to on will have to complete recovery operations the next time it is accessed?
I know, it is a "vendor - ad-hoc - fix something else" senario where the status is something I noticed but had not seen before.
I am well aware of these bastardised options and have advised the client but somehow I see them goint in one ear and out the other 🙂
many thanks,
Carlton..
January 12, 2009 at 2:24 pm
Carlton Leach (1/12/2009)
Hi Gail,so DB_2 (Status = normal) that has autoclose set to on will have to complete recovery operations the next time it is accessed?
No, that one is open and currently online. That's what the status of normal means.
When the last connection using it closes, SQL will cleanly shut it down and the status will change to closed and the is_cleanly_shutdown will go to 1
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
January 12, 2009 at 2:35 pm
Cool bananas...well except for the rest of the settings!
thanks again,
Carlton..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply