October 18, 2011 at 9:10 am
I have a database that takes over 20 mins to come back online after a reboot. It remains in the "restoring" state and is inaccessible. The error log reports that it's running the 'DBCC CHECKDB' portion of the startup, but when I manually do a 'DBCC CHECKDB' it takes just under 2 mins. The database is 5GBs in size, so not overly large. No other databases on the instance take that long to come online.
I'm also seeing problems when I attempt to restore, using the GUI. I choose the device, point to the backup file and click 'Ok'...it takes a considerable amount of time to populate the box to click 'Restore'.
What's common to those two processes? Statistics are updated weekly, as well as rebuild/reorg of the indexes.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 18, 2011 at 9:42 am
CHECKDB doesn't run on startup. The message you see in the log is the last time checkdb ran successfully (or maybe at all).
It's the recovery process that's taking too long. Some transaction needs to be rolled forward or back and it takes time.
Obviously you could be seeing some sort of IO pressure while this happens. Can't say the real issue from a distance.
Normally on a clean shutdown, without interrupting transactions, it should come back up almost instantly.
October 18, 2011 at 9:44 am
For the restore GUI, it might simply be that you have <too much> history and it takes long to run the query. There used to be a bug back in sql 2K where we had to build new indexes to support the query.
October 18, 2011 at 9:45 am
SQLDCH (10/18/2011)
I have a database that takes over 20 mins to come back online after a reboot. It remains in the "restoring" state and is inaccessible.
Restoring or recovering? Should be recovering after startup, restoring means someone restored a backup.
The error log reports that it's running the 'DBCC CHECKDB' portion of the startup, but when I manually do a 'DBCC CHECKDB' it takes just under 2 mins.
There's no checkDB run at startup. CheckDB only runs when you manually run it or have a job run it.
What's common to those two processes?
Transaction log fragmentation. Too many VLFs from an inappropriate growth increment and/or frequent log shrinks. http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply