How to find out top version store generating objects


I recently found the need to write a query to find out which tables are contributing to version store usage on the server, then you can use the following piece of T-SQL:

EXEC sp_MSforeachdb 'IF ((select DB_ID(''?'')) in (select distinct database_id from sys.dm_tran_top_version_generators)) begin print ''Database Name :'' + ''?'' + char(13) select a.*,object_name(b.object_id) as TableName from sys.dm_tran_top_version_generators a inner join ?.sys.partitions b on a.rowset_id = b.partition_id end'

The reason this got so convoluted is because the Object_Name function doesn’t accept a partition ID or a allocation hobt ID. So, I have to get the object ID from sys.partitions for the associated database and then use the object_name function.

If anyone has an alternative quicker way, then please feel to leave a comment.

Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

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