-> I was trying to add a database onto existing Always On Availability group. The database that I wanted to add was grayed out,

-> I was able to see the status for that database as “Does not meet prerequisites”. I got below message when I clicked on it,

TITLE: Microsoft SQL Server Management Studio

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.

This database lacks a full database backup. Before you can add this database to an availability group, you must perform a full database backup.

-> I checked the database properties and was able to see that “Auto close” option was enabled for the database.

-> Auto close option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

-> Due to above reason we decided to disable Auto close option.

-> You can use below query if you dont want the GUI method. I just scripted below query from GUI “Script” option.

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

-> Once the database JBSDB auto close option was disabled, I was able to add the database onto Availability group without any issues,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.