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.

Why is VSS complaining about SQL Data files

I recently came across a question on why a NTBackup of a Text File on a drive reported the following error in the application event logs:

Event Type:    Error
Event Source:    VSS
Event Category:    None
Event ID:    6005
Sqllib error: Database %1 is stored on multiple volumes, only some of which are being shadowed.

The interesting part is that the drive that stores the Text file that I am backing up also has a LDF file for a database. The MDF file of the same database resides on another drive. The intelligent SQL Writer was able to find out that only one file for the database in question is on this drive and the other file(s) is not present on the same drive. Hence, it reported the error causing confusion in our minds. A red cross in any log file is a BAD thing!! Not in this case. 🙂

I have already blogged about the behavior of NTBackup/VSS when a Snapshot is created in a previous post.

The conclusion is that if you have database files residing on multiple drives and you are using VSS to backup non-SQL database files on only one of these drives, then you can ignore the above error (if that is the only error). This is yet again another of the infamous by-design behaviors! 🙂