Version

Argent Advanced Technology all versions

Date

Wednesday, 6 May 2015

Summary

When a heavily used SQL Server is monitored by Argent for SQL Server, the databases may suddenly become unresponsive

The SQL Server log shows repeatedly lines like following:

Unless the disk is full or server is experiencing hardware failure, the issue can be temporarily resolved by taking database offline and putting back online immediately

However the issue can happen again if database option ‘Auto Close’ is set to true

According to Books Online:

When set to OFF, the database remains open after the last user exits

The option is deemed obsolete and setting it to True is described as Worst Practice

As matter of fact, the option was set to False by default in all SQL Server versions except SQL Server Express 2008 and earlier

When running Argent for SQL Server Rules especially with the option ‘Whole’ server, Argent AT Engine enumerates all the databases, and check metrics in order

If the ‘Auto Close’ option is set to True, the monitoring can cause SQL Server loading and unloading databases constantly

If the Rules are executed frequently, not only SQL Server is unnecessarily pressured, the chance of transaction deadlock is also greatly increased

When deadlock happens, the database is stuck in error ‘9001’ until it is taken offline and put back online

Technical Background

The issue usually happens for SQL Server Express 2008 and earlier including MSDE 2000

The ‘Auto Close’ option can also be True if the database was upgraded from the Express version

Run following query to find out what databases have the issue:

The sample Rule ‘CHECK_AUTO_CLOSE_OPTION_SQL_EXP_2008_CHECK’ in Argent for SQL Server can detect this setting too

Resolution

Customer needs to run SQL Server Management Studio and manually set ‘Auto Close’ option to False for each database, or runs following query to update: