Just a few days ago, we observed that our daily differential backup job failed on one of our not so busy servers.
On checking the error log, it came across that the backup statement got terminated as the transaction log file for the target database was not available.
At first it seemed as if someone had stepped over the log file of the database and now we would either have to recreate the database using the FOR ATTACH_REBUILD_LOG option or we would need to look for the backups.
Before we would tread in that direction, we checked whether the log file was actually present at its marked location on the hard drive. The file was very much sitting there and further we tried to access the database and we were able to do that successfully.
However, when we tried to run the backup statement manually on the affected database and it would still come back with a similar error indicating that it was not able to see the log file.
This was a peculiar issue wherein on one hand the database was accessible and on the other, certain specific database operations would fail indicating their inability to read the transaction log file.
I thought it was worth giving a look at the status of this database in the sys.databases catalog view. And yes of course, a simple SELECT * statement from sys.databases lead us to the right direction.
We observed that the affected database that caused the daily differential backup job to have failed, had the Auto_Close property turned ON while rest all the databases on the server instance had it turned off.
Auto_Close – Auto_Close is an option that simply closes off the database when it is not in use. It can cause serious performance degradation as it requires opening and closing the database after each connection. Also, this feature when kept turned on; it flushes the procedure cache after each connection.
So now, our work was etched out clearly! All we had to do was to set the Auto_Close off for this database and then try to run the Backup statement again.
It was not so straightforward though! We were unable to change the Auto_Close setting as the log was unavailable.
Finally, to fix this and to make the log available back again, we had to set this database offline and then bring it back online using the commands given below.
ALTER DATABASE [nameofdatabase] SET OFFLINE WITH
ALTER DATABASE [nameofdatabase] SET ONLINE
Once we brought the database back online, we were able to set the Auto_Close option to OFF and were also able to run the Backup Statement successfully.
We used the following statement to set the Auto_Close option to OFF.
ALTER DATABASE [nameofdatabase] SET AUTO_CLOSE OFF
It is always recommended to keep the Auto_Close option turned off for all your SQL Server databases and this incident has helped us understand and relate to the importance of the same.