Wednesday, May 1, 2013

Starting up database message in ERROR.log file


We have noticed that one SQLServer Database is starting very frequently and also it is in "Recovery Pending" mode.

Below error messages from SCOM
Database WEBAPP in SQL Server instance INST1 on computer CLUST.sqlserver-expert.com is offline/recovery pending/suspect/emergency

Below messages from  ERROR.LOG
2013-05-01 12:06:53.29 spid122     Starting up database ‘WEBAPP’.
2013-05-01 12:08:04.20 spid123     Starting up database ‘WEBAPP’.
2013-05-01 12:09:14.25 spid184     Starting up database ‘WEBAPP’.

Solution:- You will see this issue then AUTO CLOSE option is set to TRUE in Database properties.

If you set AUTO_CLOSE to TRUE then the database will be closed as soon as there are no connections to it and database will re-open every time a connection is established to it. 

There is no much information from log. But you will see this error when backup is running, because backup job trying to create a  new connection to the database.

Set AUTO CLOSE option to FALSE to resolve this issue.

Management Studio --> Select the Database "WEBAPP" -->  Right Click  Properties--> Options --> "Auto Close" value to "False"

Regards
Satishbabu Gunukula

2 comments:

  1. I see that these facts are something to consider. Database is the foundation of a system and for some errors as such, it could cause a great trouble.

    ReplyDelete
  2. But what if you are receiving this error and the database are already set to false

    ReplyDelete