Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We ran across an error today:
Error: 3967, Severity: 17
and from what I can tell it's basically 'full version store'. This has raised some questions that I've had trouble answering, as at my shop we've never had this error before:
If there is a preset max size (based on total tempdb size or otherwise), is it dynamic?
Is there a setting that would make it dynamic if it isn't?
Can we set up alerts based on this? (i.e. DMVs to use to track size etc.)
Severity 17 indicates SQL Server ran out of a required resource. You typically see this when a database runs out of space. In this case, it sounds like tempdb is being used for read committed snapshot isolation version rows for a query that needed to spill to tempdb.
This could be quite common if tempdb has a limited maximum size and a poorly written query is attempted that, for instance, performs a very large cross join.
You can use this query to see the current size of the version store in tempdb:
SELECT *
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name LIKE 'Version %';
Technet has an interesting article on version store usage at https://technet.microsoft.com/en-us/library/cc966545.aspx#EDAA
This MSDN page mentions error 3967:
When tempdb runs out of space, the Database Engine forces the version stores to shrink. During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. A message 3967 is generated in the error log for each victim transaction. If a transaction is marked as a victim, it can no longer read the row versions in the version store. When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. If the shrinking process succeeds, space becomes available in tempdb.
Neither of the pages I've linked above contain explicit advise about the maximum size of the version store in tempdb, which leads me to believe the only limitation is the maximum configured size of the tempdb itself, minus whatever space is being used in tempdb for other objects.
