October 20, 2010 at 12:05 pm
A coworker with a 2008 instance on his PC needed to upgrade to 2008 R2. He had detached several databases before starting the upgrade. When reattached, some but not all of the databases went into recovery mode. He sought my help with this; I hit refresh in the Object Explorer pane of Management Studio, and the "in recovery" went away. I assumed that SQL had seen something wrong and fixed it and SSMS just needed to have its screen updated.
However, after I left, further refreshes showed the databases to be jumping back in and out of recovery mode, as often as several times a minute. This went on for 2 days. The databases are not large (< 4GB data & log) and there's no traffic to them. The PC is a decent workstation running XP Pro.
I looked at the SQL logs to see what was up, and discovered that the databases in question all had AutoClose set to True. Setting this back to False cured the problem.
Just to test, we detached & reattached a database where AutoClose was set to False. It came back as set to True. I checked Model and it was set to False. We were using SSMS for all of the attaching and detaching.
Anyone know where the AutoClose = True is coming from?
October 20, 2010 at 12:58 pm
When you're performing the detach/attach, might try scripting the action to new query window and see if somethings setting it there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply