SQL SERVER – Error – Auto Close is Enabled. Only Databases with Auto Close Disabled can be Added to an Availability Group

As a part of my consulting job, I do work with clients who wanted to deploy AlwaysOn availability group. Sometimes there are some simple errors which are encountered, but new DBAs are not aware of such things. In this blog we would talk about one of such error.

While using AlwaysOn availability group wizard, to deploy AlwaysOn AG, here is the error which was encountered.

Auto close is enabled. Only databases with auto close disabled can be added to an availability group. To turn off auto close, set the Auto Close database property to False.
To be added to an availability group, this database must be set to the full recovery model. Set the Recovery Model database property to Full and perform a full or differential database backup on the database. You will then need to schedule log backups on the database.

Here is an interesting this about above wizard, which I noticed. If there is just one condition which is not met, then hyperlink shows the cause. If there are multiple causes, then it displays “Does not meet prerequisites”. When we click on the hyperlink we get all causes in a pop-up.

In this client’s situation, we have two problems:

1. The auto close setting was ON for the database.
2. The database was set to simple recovery model.

SOLUTION/WORKAROUND

As the error message in blog title mentions, we need to disable the auto close off for the database. There are multiple ways to do it.

SQL Server Management Studio:

1. Open SQL Server Management Studio and then connect to SQL Server Instance.
2. Right click the user database and then click on Properties from the drop-down menu.
3. Under Select a Page on the left pane, click on Options and then set the value as FALSE for AUTO CLOSE.
4. Click OK to save the changes

T-SQL

We can use below T-SQL to change the setting for a database called SQLAuthority.

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

What are the other errors which you have seen on this screen? Please share the problem and the solution via the comments section below.

Reference: Pinal Dave (https://blog.sqlauthority.com)