Have you ever encountered the error message “Version store is full. New version(s) could not be added” while working with SQL Server? If so, you’re not alone. In this blog post, we will discuss this error and explore the concept of the version store in SQL Server.

The Situation

Recently, one of my clients who had configured an Always On Availability Group approached me with a strange behavior. They noticed that the redo queue size was continuously increasing on the secondary replica. Upon further investigation, I found that the redo process was working, but it was waiting for the LATCH on APPEND_ONLY_STORAGE_FIRST_ALLOC.

Additionally, the SQL Server ERRORLOG revealed a continuous message stating “The version store is full. New version(s) could not be added.” This error message indicated that there was a problem with the version store, which is closely related to the secondary replica.

Understanding the Version Store

In SQL Server, the version store is a part of the TempDB database and is used to support the functionality of features such as snapshot isolation and read-only secondary replicas in Always On Availability Groups.

When a secondary replica is readable, all queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. This means that the version store comes into play when the secondary replica is being accessed for read operations.

The Error and Solution

In the case of my client, the version store was full, preventing new versions from being added. This was due to a serious space issue on the drive where the TempDB database was located. As a result, the TempDB was unable to grow and generate new versions.

To resolve this issue, we needed to disable reads from the secondary replicas. By disallowing reads from the secondary replicas, we were able to free up space in the version store and allow the redo process to pick up speed.

Here is the command we used to disable reads from the secondary replicas:

USE [master]
GO
ALTER AVAILABILITY GROUP [PROD_AG]
MODIFY REPLICA ON N'Godzilla_2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = NO))
GO

After disabling the reads, the version store vanished, and the redo process was able to proceed without any issues. We also took the opportunity to shrink the TempDB to avoid future space issues on the drive where it was located.

Conclusion

The version store in SQL Server plays a crucial role in supporting features such as snapshot isolation and read-only secondary replicas. However, it is important to monitor the space usage of the version store and ensure that there is enough space available for it to function properly.

If you encounter the “Version store is full” error, it is likely due to a space issue in the TempDB database. Disabling reads from the secondary replicas can provide a temporary solution to alleviate the problem.

Remember to regularly monitor the space usage of the version store and take appropriate actions, such as increasing the size of the TempDB or optimizing queries, to prevent this error from occurring in the future.