SQL SERVER – SQL Real Cases – Issue with auto_close option (Root cause of SUSPECT database).

Days ago I was working to find out the root cause of a SUSPECT state in a productive database. When I started to work in the database, it was already online. Somebody restart the MSSQL service and it bring online the database, but nobody knows what was the cause of the issue, it task is my job. :-)

Please be aware that the database name, data file or log file names are not the real values.

1. First I saw that the size of Transaction Log was several times the size of the entire db. It is bad, but this is not the cause of the suspect database.

2. I started to checking the ERRORLOG and I saw the system was configured to archive almost 60 ERROLOG. It is strange.

3. I started to read all the ERROLOG files, one by one. How to check it?, please check SQL SERVER – SQL TIPS – SELECT the ERROLOG. or SQL SERVER – Working with ERRORLOG.

4. When I was checking the ERROLOG.10, I saw the following error:

2009-07-03 22:40:02.78 spid51 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\Productive_DB_LOG_FILE.ldf. 0

2009-07-03 22:40:02.78 spid51 FCB::Open failed: Could not open device c:\ Productive_DB_LOG_FILE.ldf for virtual device number (VDN) 2.

In the first error I can see that the log file can not be blocked by SQL Server. We knows that SQL Server block the data files and log files when it try to start the database. The issue here is, the database was not offline at any moment. The system was working during long period of time and I did not see any message where the database was offline. So, if the database was ONLINE, what was the cause of the log file was not blocked by SQL Server?

At this moment I knew that the cause of the database was in SUSPECT state, was because a log file for the database was blocked by other process and can not be blocked by SQL Server when it try start the database.

5. When I was checking the ERRORLOG files, I saw several messages (not errors) as the following.

2009-07-03 22:33:08.85 spid51 Analysis of database 'PRODUCTIVE_DB' (9) is 100% complete (approximately 0 more seconds)

2009-07-03 22:40:02.74 spid51 Starting up database 'PRODUCTIVE_DB'.

Mmmmmm Starting up database several times?,

What is the cause that the database is starting in several times? I thought in the option AUTO_CLOSE at db level. SQL Server has an option that close the database automatically when the last user close his connection to database. It option is AUTO_CLOSE.

6. Then I checked if the AUTO_CLOSE is enabled with the next statement:

SELECT DATABASEPROPERTYEX('PRODUCTIVE_DB','ISAUTOCLOSE')

Result.

1

If IsAutoClose is 1, the option is enabled.

As per SQL Server BOL.

IsAutoClose: Database shuts down cleanly and frees resources after the last user exits.

7. Conclusion.

At this moment I know that AUTO_CLOSE option is enable. So, if this option is enable, it cause that SQL Server frees the resources of the database after the last user finish his connection and when a new user is connected to the database, it is started and it generate a message of “Starting Database” in the ERROLOG files. Hence, I am ready to conclude: AUTO_CLOSE option is enabled, this option caused that all resources are frees and caused that the data files and log files of the database are not blocked anymore, then this is cause that another process was able to block the log file and it happened, finally, when SQL Server try to start the database and block the log file again, it was not possible and SQL Server marks in SUSPECT state the PRODUCTIVE_DB.