May 30, 2011 at 9:48 am
The DBs in my instance are frequently going into recovery mode.
A select * from sys.databases where name='DBName' renders the following results (note: no STATUS column)
sys.objects Column name sys.objects Column Value
==========================================
name DBName
database_id 18
source_database_id NULL
owner_sid 0x010100000000000512000000
create_date 03 January 2011
compatibility_level 100
collation_name NULL
user_access 0
user_access_desc MULTI_USER
is_read_only 0
is_auto_close_on 1
is_auto_shrink_on 1
state 0
state_desc RECOVERING
is_in_standby 0
is_cleanly_shutdown 1
is_supplemental_logging_enabled 0
snapshot_isolation_state 0
snapshot_isolation_state_desc OFF
is_read_committed_snapshot_on 0
recovery_model 1
recovery_model_desc FULL
page_verify_option 2
page_verify_option_desc CHECKSUM
is_auto_create_stats_on 1
is_auto_update_stats_on 1
is_auto_update_stats_async_on 0
Other than this, I have no real clues to go on.
I will be starting a scandisk later tonight and will defrag my hard drive.
I will also relocate 1 or 2 of the DBs to another drive and monitor it from there - but currently I have no clues as to why 1 DB may go into recovery mode and not the other. I also have no clue as to why it eventually resolves the recovery issue on its own. And I have no clue as to what it did to "recover" itself.
I suspect that the 8501 error that I was getting on the 1 DB is going to come back and bite me again soon 🙁
May 30, 2011 at 9:52 am
Set auto_close and auto_shrink to OFF.
Rebuild all indexes and update all stats at your next maintenance window.
May 30, 2011 at 9:58 am
Ninja's_RGR'us (5/30/2011)
Set auto_close and auto_shrink to OFF.
+1000000
Recovering means simply that SQL closed your database (likely because auto_close was on) then next time the DB is needed it has to run recovery on it. While it's running recovery, the database state will be RECOVERING and the DB will be unavailable.
It's not a symptom of corruption. It's not a symptom of hardware problems. It's simply that SQL has to recover databases when they are brought online. That will happen after a restart of SQL or when a DB that's set to auto_close is accessed.
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 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply