July 22, 2009 at 11:48 am
SSC folks:
I am hoping someone here can shed some light on an issue that has occurred with one of my database servers. I have a fresh copy of SQL2008 (10.0.2714.0) that has one database on it. The database is partitioned, and data is broken up into over 40 filegroups, has a total size of just over 6.95 TB, and is in Simple recovery mode. This database is used to store over a year's worth of our data that has been inserted via an SSIS package from flat files.
I had to cycle the SQL service on the machine, which was around 8 AM EST after making a change to the SQL server configuration facet: RemoteAccessEnabled. After cycling SQL, the sole database has been listed as "In Recovery".
Typically, I would not be concerned, as I have seen this occur before with two of our SQL 2005 databases (in a clustered environment), which usually recover in no more than 5-10 minutes, and exponentially larger than this database. BUT - with those DB's, as should be, I would see information being written to the system and SQL logs about the recovery progress. This is a different story - as there is no indication in the SQL or Windows 2003 system/application logs about a recovery progress indicator. This is my first foray into using SQL08, so I'm a little inquisitive at this point.
Is this typical of SQL2008? Should I be concerned? The data can be recovered easily and re-inserted, but it would take me another 3-4 weeks of processing 15 GB daily flat files, which I really don't want to spend the time babysitting again. The data had finished inserting via SSIS last night around midnight, so no data has touched the DB since then.
A) Can someone provide some advice? Should I restart the machine/SQL since nothing is being shown in the logs about recovery time?
B) Can someone also explain how/when a checkpoint is issued in SQL08 in such a scenario (and also explain if this could prevent a long-running recovery procedure to occur)?
Thanks all,
-Ken
July 22, 2009 at 1:03 pm
Can you post the current SQL error log? There are a number of reasons why this might be happening, from benign to serious, need to see the error log to say for sure.
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 23, 2009 at 10:36 am
Thanks for the offer; the DB did come out of recovery. However, I found it strange that no indicators of progress (or a recovery procedure) were being written to the error logs.
False alarm, but thanks anyhow.
In regards to my previous question - can someone explain how the engine handles checkpoints and why/when a recovery procedure occurs? What exactly is takign place... is there any good reading on this material, for my future knowledge?
Thanks!
-Ken
July 23, 2009 at 11:52 am
Try this for starters
http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/
Recovery is only necessary when the DB has not been cleanly shut down, usually the result of an unexpected, sudden shutdown of the service or the server.
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