ASPState Database on SQL AlwaysOn Availability Group

Running your ASPState database in a SQL Always On Availability Group provides redundancy in case there is a SQL server failure. Unfortunately the default process for setting up the ASPState database does not take this configuration into account and you may find that your ASPState database has an ever expanding data file that is using all of your disk space.

When the ASPState database is created, there is also a SQL Agent job that is also created which deletes expired sessions to keep the database size at a reasonable level. 

This job is usually called: ASPState_Job_DeleteExpiredSession

In the usual setup, this job runs every minute and simply fires of a Store Procedure which lives in the ASPState database itself using the following T-SQL statement:

Execute DeleteExpiredSessions

Generally this all works well. The issue arises when the Availability Group is failed over to a secondary availability replica. Because the SQL Agent job is not present on this server, the expired sessions are no longer deleted and the database begins to grow.

You may notice on the SQL server that was originally the primary availability replica that there are errors in the SQL Agent job history similar to this:

Date xxxxxxxxxxx
Log Job History (ASPState_Job_DeleteExpiredSessions)

Step ID 1
Server [serverName]
Job Name ASPState_Job_DeleteExpiredSessions
Step Name ASPState_JobStep_DeleteExpiredSessions
Duration 00:00:08
Sql Severity 16
Sql Message ID 3906
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: [domain\user]. Failed to update database "ASPState" because the database is read-only. [SQLSTATE 25000] (Error 3906). The step failed.

This is because the database the job is trying to run against has now become the read-only secondary replica database. If you are quickly running out of disk space and need to fix this problem, then log on to the primary replica and simply run the following:

USE [ASPState]
GO

EXECUTE DeleteExpiredSessions

The next logical step would be to simply set up a matching SQL Agent Job on the all servers that will delete the old sessions regardless of which server is the primary replica at the time. This is definitely the next step, however this will continue to generate errors on all the servers that are running as secondary replicas.

Fortunately though, SQL Server provides a better long term solution that will resolve the issue and save us writing out unnecessary errors. All we need to do is update the T-SQL statement that the Agent jobs run (on each of the replica servers) to look like the following:

IF sys.fn_hadr_is_primary_replica('ASPState') = 1
BEGIN
EXECUTE DeleteExpiredSessions
END
ELSE
BEGIN
SELECT 'This server is not the primary replica for ASPState at this time, skipping maintenance.'
END