Do you see things a little differently?

Friday, August 2, 2013

MS SQL server tells me ->The log for database "databasename" is not available<-

I assisted one of my employer's remote sites with setting up SQL Express 2008R2 for use with two separate applications. The first one is a custom in house quoting tool, and the second is for a commercial product called Lab Wizard. There have now been two distinct occasions where the system stops working as it should and the error message "The log for database DBName is not available". The first time I didn't really know what to make of it, and I was able to restore function by taking the databases offline, then bring them back online. Thinking that maybe it had something to do with logging, I set the databases to simple mode and waited. It took almost a week, but the problem resurfaced. This time after a bit more research I came back with suggestions that the server did not have enough resources and was becoming memory and/or I/O constrained.

The server is virtual, so I shut it down and added RAM bringing the system system from 4GB to 12GB of RAM. I honestly can't believe this is the issue. My suspicion is that the Lab Wizard application doesn't play nice with the database. One other suggestion I saw was to turn Auto Close off for that database. If using the non Express versions of SQL Auto Close is turned off by default. On the Express version it is on by default. ( http://msdn.microsoft.com/en-us/library/ms190249%28v=sql.105%29.aspx ) To turn off Auto Close, run the following.
 

ALTER DATABASE [DBNAME] SET AUTO_CLOSE OFF WITH NO_WAIT 

To do this for all Databases on the server run the following... execute 

sp_MSforeachdb 'ALTER DATABASE [?] SET AUTO_CLOSE OFF WITH NO_WAIT' 

I will let this run and check back with the site next week. If this doesn't solve the problem, this post will be updated with what I find.