SqlServer – Tempdb – Error – “Insufficient space in tempdb to hold row versions”

Background

Here reviewing failed jobs and tried to re-run one of them, but still No Go!

TroubleShooting

Error

Image

Textual

Insufficient space in tempdb to hold row versions.
Need to shrink the version store to free up some space in tempdb.
Transaction (id=17270854 xsn=2470235 spid=79 elapsed_time=3101) has been marked as victim and it will be rolled back if it accesses the version store.
If the problem persists, the likely cause is improperly sized tempdb or long running transactions.
Please refer to BOL on how to configure tempdb for versioning.

 

Query

Top Version Store Occupants

Let us see what Objects are in the Version Store.

Code



set nocount off;
go

declare @tblHOBT TABLE
(
	  [id]			int not null identity(1,1)
	, [dbid]		int not null
	, [database]	sysname not null
	, [schema]		sysname not null
	, [object]		sysname not null
	, [objectType] sysname not null
	, [index]		sysname null
	, [hobtID]		sysname not null

)

insert into @tblHOBT
(
	  [dbid]
	, [database]
	, [schema]
	, [object]
	, [objectType]
	, [index]
	, [hobtID]
)
EXECUTE master.sys.sp_MSforeachdb 
	'
		if databasepropertyex(''?'', ''Collation'') is not null
		begin

			USE [?]; 
			select distinct
				      db_id()
					, ''?''
					, tblSS.[name]
					, tblSO.[name]
					, tblSO.[type_desc]
					, tblSI.[name]
					, tblSP.[hobt_id]

			from   sys.objects tblSO

			inner join sys.schemas tblSS

				on tblSO.schema_id = tblSS.schema_id

			inner join sys.indexes tblSI

				on tblSO.object_id = tblSI.object_id

			inner join sys.partitions tblSP

				on  tblSI.object_id = tblSP.object_id
				and tblSI.index_id = tblSP.index_id

			-- On User Table
			where tblSO.[type] = ''U'' 


		end

	'

select 
		[rowNumber]
			= ROW_NUMBER() 
			OVER
				(
					ORDER BY 
						  [database] ASC
						, [schema]   ASC
						, [object]   ASC
						, [index]    ASC
				) 

		, [database] 
		, [schema]   
		, [object] 
		, [objectType] 		  
		, [index]   
		, [count]
			= count(*)
		, [aggregatedRecordLengthInBytes]
			= sum
			(
				aggregated_record_length_in_bytes
			)
		, [aggregatedRecordLengthInKB]
			= sum
			(
				aggregated_record_length_in_bytes
			)
			/ (1024)

from   @tblHOBT tblHOBT

inner join sys.dm_tran_top_version_generators AS tblSTTVG

		on tblHOBT.[dbid] = tblSTTVG.database_id
		and tblHOBT.[hobtID] = tblSTTVG.rowset_id

group by
		  [database] 
		, [schema]   
		, [object]   
		, [objectType] 
		, [index]   

order by 
		  [database] asc
		, [schema]   asc
		, [object]   asc
		, [index]    asc


Output

 

Monitor TempDB Disk Space Used

Monitor Tempdb Disk Space Used By Type ( sys.dm_db_file_space_usage )

Outline

Let us query sys.dm_db_file_space_usage to divvy up tempdb storage allocation by type.

Code

use [tempdb]
go

select 
		  getdate() AS runtime

		, [userObjectKB]
			= SUM (user_object_reserved_page_count)*8

		, [userObjectMB]
			= (SUM (user_object_reserved_page_count)*8) / (1024)

		, [internalObjectKB]
			= SUM (internal_object_reserved_page_count)*8

		, [internalObjectMB]
			= SUM (internal_object_reserved_page_count)*8 / 1024

		, [internalObjectGB]
			= SUM (internal_object_reserved_page_count)*8 / (1024 * 1024)

		, [versionStoreKB]
			= SUM (version_store_reserved_page_count)*8

		, [versionStoreKB]
			= (SUM (version_store_reserved_page_count)*8) / ( 1024)

		, [mixedextentKB]
			= SUM (mixed_extent_page_count)*8

		, [mixedextentMB]
			= SUM (mixed_extent_page_count)*8 / 1024

		, [freeSpaceKB]
			= SUM (unallocated_extent_page_count)*8

		, [freeSpaceMB]
			= SUM (unallocated_extent_page_count)*8
				/ ( 1024)

		, [freeSpaceGB]
			= SUM (unallocated_extent_page_count)*8
				/ ( 1024 * 1024)

FROM [tempdb].sys.dm_db_file_space_usage



Output

Explanation
  1. User Object is 1 MB
  2. Internal Object 166 GB
  3. mixed extent is 7 MB
  4. free space is 18 GB

 

 

Monitor Disk Space Used By Object ( tempdb.sys.system_internals_allocation_units )

Outline

Let us query tempdb.sys.system_internals_allocation_units and tempdb.sys.system_internals_partitions to gather storage allocation by object.

Code

select 
		  [object]
			= quoteName(object_schema_name(tblSIP.object_id))
				+ '.'
				+ quoteName(object_name(tblSIP.object_id))

		, tblSO.[type_desc]

		, [totalPages] 
			= sum(tblSIAU.total_pages)

		, [totalKB] 
			= sum(tblSIAU.total_pages * 8)

from   tempdb.sys.system_internals_allocation_units tblSIAU

JOIN   tempdb.sys.system_internals_partitions tblSIP

		ON tblSIAU.container_id = tblSIP.partition_id

inner join sys.objects tblSO
	on tblSIP.object_id = tblSO.object_id

where  tblSO.[type] not in 
		(
			'S'
		)

group by
		  tblSIP.[object_id]
		, tblSO.[type_desc]

order by
		sum(tblSIAU.total_pages) desc


Output

Explanation
  1. Space usage for individual objects is very miniscule

Summary

An alarm, through logging in the error log, is raised due to inability to grow the “Version Store“.

We checked sys.dm_tran_top_version_generators to see which objects are currently hogging the Version Store, we identified hangFire.server and msdb SQL Server Agent Job related tables.

We don’t really appear to have high uptake.

We went and looked at [tempdb].sys.dm_db_file_space_usage.  And, the biggest occupant seems to be internal objects.

Both tempdb.sys.system_internals_allocation_units and tempdb.sys.system_internals_partitions have very low numbers.

Conclusion

Our problem is not Version Store nor actual tables (system, user, temp ).

But, internal objects …

sys.dm_db_file_space_usage (Transact-SQL)
Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s