One of the db was showing its state as “In Recovery “on
several times per day. As we know this state comes when there is a recovery
process happening on db due to server shutdown restart or for any restore
operations but there was no such a case happened.
After googling find the issue may be caused due to table
corrupt on db, Log file full on disk, IO subsystem not functioning correctly
and also server restarted frequently. When checked these cases didn’t find anything happened
related to these on server. Then I saw an article which stated reason due to
Database option “Auto Close” was “True” . When I switched it to false it worked
and database was In online state.
But here comes the investigation part “how Auto Close
option went to true as normally its state has to be false?
Further checked and found the db was attached recently
and it was Express Edition which by default makes the db option “Auto Close” to
true when it is attached. Finally to
avoid these issues in future created a job which checks the db settings
periodically and alerts if there is a change in the db options.
No comments:
Post a Comment