SQL Server – Review LOB Data – File groups & Allocated Storage

Background

Wanted a quick look at our LOB data.

The filegroups they are stored in and their allocated storage.

Code




-- To see lob_data filegroups
SELECT 
		  objectName 
			=  
				 tblS.[name]
				+ '.'
				+ OBJECT_NAME(tblO.object_id)

		, [indexName]
			= tblSI.[name]

		, [indexType]
			= tblSI.[type_desc]

		, fileGroupName 
			= FILEGROUP_NAME(tblAU.data_space_id)

		, tblAU.[type_desc]

		, [totalMB]
			= (tblAU.total_pages* 8 ) / 1024

		, [usedMB]
			= (tblAU.used_pages* 8 ) / 1024

FROM sys.objects tblO

INNER JOIN sys.schemas tblS

		on tblO.schema_id = tblS.schema_id

INNER JOIN sys.indexes tblSI

		on tblO.object_id = tblSI.object_id


INNER JOIN sys.partitions tblP

		on  tblSI.object_id = tblP.object_id
		and tblSI.index_id = tblP.index_id

JOIN sys.allocation_units tblAU
		on tblP.partition_id = tblAU.container_id

/*
	Object Type is User Table
*/
WHERE tblO.[type] = 'U'

/*
	Allocation Type is LOB Data
*/
AND   tblAU.[type_desc] = 'LOB_DATA'


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