Yesterday, I was approached by two of the system admins, stating that one of the database they are connecting to is having issue.  They are attempting to connect via a perl script, which I could care less about, not because perl suck or anything, because it doesn’t.  My goal is to keep SQL instances and databases humming along smoothly.

First thing I did was to look at the dataase via SSMS to see if it was in any kind of suspected or recovery mode.  When I opened SSMS, it showed (in recovery), so I looked at the error log, using xp_readerrorlog, and I noticed that the database was opening and closing everything a connection to it is made. Also, it was running a CHECKDB on the database everytime it came back online.  And I think this is why the perl script was failing.  It was timing out to quickly, although, the two sysadmins didn’t give me the error they got, I can only guess.  Having found this out, I went ahead and disabled the auto-closed.  All is well.

You can pretty tell that the database is in auto-closed mode because in the sql error log, you will see a lot of “Starting up database ‘dbname’ ” along with:

SQL server has encountered 4 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to some database maintenance …

SQL server has encountered 4 occurrence(s) of cachestore flush for the ‘SQL Plans’ cachestore (part of plan cache) due to some database maintenance …

SQL server has encountered 4 occurrence(s) of cachestore flush for the ‘Bound Trees’ cachestore (part of plan cache) due to some database maintenance …

Further, this database was in Bulk-Logged recovery mode so I performed a database & log backup of this database because the log file was 680GB!  It is a good thing that database and log backup only backup data and not empty spaces.  This morning, the log backup completed and the log file was still at 680GB.  I ran my ‘freespace’ script, which you can download in the scripts and applications section.  It showed that the log file has 99% free space and only 6GB was being used.

I ran my ‘shrinkdb’ script and it didn’t reduce the size of the log file, instead only increased it.  After a few different methods, I just went ahead and changed the recovery mode from Bulk-Logged to Simple, shrink the log file, and return the database to Bulk-Logged.  Lastly, I changed the database growth setting from default growth by % to 1024mb.