The joy of FILEGROUP_NAME (and probably other as well)

Part of my scripts collection, is a script that returns the details of a table: storage, metadata (sys.objects), partitioning, etc. I was using this script for quite a while and ran ok. Few days ago, however, after I disabled an index on a partitioned table it failed and the error was a bit puzzling:

Msg 220, Level 16, State 1, Line 8
Arithmetic overflow error for data type smallint, value = 65601.

An excerpt of the whole script that failed is here (adapted to use a test database I built with this script – remove the .DOC extension):

USE [TestFG]

DECLARE @tablename VARCHAR(256), @schemaname VARCHAR(64)

SET @tablename = 'test_fg'
SET @schemaname = 'dbo'

/* physical storage */
SELECT 'Physical storage info' AS Category

;WITH spaceused AS
	COALESCE(f.[name], FILEGROUP_NAME(ds.[data_space_id])) AS fg_name,
	OBJECT_SCHEMA_NAME(p.[object_id], DB_ID()) + '.' + OBJECT_NAME(p.[object_id]) AS table_name,
	OBJECTPROPERTY(p.[object_id],'IsMSShipped') AS system_object,
	i.[name] AS index_name,
	i.[type_desc] AS index_type,
	COALESCE(ps.[name],'unpartitioned') AS ps_name,
	COALESCE(pf.[name],'unpartitioned') AS pf_name,
	/* number of indexes, excluding the clustered index */
	COALESCE((SELECT COUNT(1) FROM sys.indexes WHERE [index_id] > 1 AND [object_id] = p.[object_id]),0) AS num_of_indexes,
	(SELECT SUM([ROWS]) FROM sys.partitions WHERE [index_id] < 2 AND [OBJECT_ID] = p.[object_id]) AS num_rows,
	(SELECT SUM(reserved_page_count) * 8 FROM sys.dm_db_partition_stats WHERE [OBJECT_ID] = p.[object_id]) AS reserved_space_KB,
	(SELECT SUM(reserved_page_count) * 8 FROM sys.dm_db_partition_stats p1, sys.internal_tables it WHERE 
				it.parent_id = p1.[object_id] AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p1.[object_id] = it.[object_id]
			AND p1.[object_id] = p.[object_id]) AS xml_ft_reserved_space_KB,
	(SELECT SUM(used_page_count) * 8 FROM sys.dm_db_partition_stats WHERE [OBJECT_ID] = p.[object_id]) AS used_space_KB,
	(SELECT SUM(used_page_count) * 8 FROM sys.dm_db_partition_stats p1, sys.internal_tables it WHERE 
				it.parent_id = p1.[object_id] AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p1.[object_id] = it.[object_id]
			AND p1.[object_id] = p.[object_id]) AS xml_ft_used_space_KB,
				WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
				ELSE lob_used_page_count + row_overflow_used_page_count
			) * 8 FROM sys.dm_db_partition_stats WHERE [OBJECT_ID] = p.[object_id]) AS data_space_KB
FROM    sys.partitions p WITH(NOLOCK) 
LEFT JOIN sys.indexes i WITH(NOLOCK)  ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.data_spaces ds WITH(NOLOCK)  ON i.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_schemes ps WITH(NOLOCK)  ON ds.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf WITH(NOLOCK)  ON ps.function_id = pf.function_id
LEFT JOIN sys.destination_data_spaces dds with(nolock)  ON dds.partition_scheme_id = ds.data_space_id AND p.partition_number = dds.destination_id
LEFT JOIN sys.filegroups AS f WITH(nolock) ON dds.data_space_id = f.data_space_id
	/* Only get clustered tables / heaps */
	p.[index_id] <= 1

	CAST((	SELECT	SUM(([size] * 8) / 1024.) AS [fg_size_MB],
					SUM((FILEPROPERTY([name],'SpaceUsed') * 8) / 1024.) AS [fg_used_MB],
					SUM(([size] * 8) / 1024.) - SUM((FILEPROPERTY([name],'SpaceUsed') * 8) / 1024.) AS [fg_free_space_MB],
					100 * (SUM((FILEPROPERTY([name],'SpaceUsed') * 8) / 1024.) / (SUM(([size] * 8) / 1024.) )) AS [fg_percent_used],
					COUNT([file_id]) AS [fg_number_of_files]
			FROM sys.database_files df
			WHERE FILEGROUP_NAME([data_space_id]) = fg_name
			FOR XML PATH(''), ROOT('filegroup')) AS XML) AS [fg_space_details],
	CAST((	SELECT	[name] AS [logical_name], 
					([size] * 8) / 1024. AS [size_MB],
					(FILEPROPERTY([name],'SpaceUsed') * 8) / 1024. AS [used_MB],
					100 * ((FILEPROPERTY([name],'SpaceUsed') * 8) / 1024.) / (([size] * 8) / 1024. ) AS [percent_used],
					(([size] * 8) / 1024. ) - ((FILEPROPERTY([name],'SpaceUsed') * 8) / 1024.) AS [free_space_MB]
			FROM sys.database_files df
			WHERE FILEGROUP_NAME([data_space_id]) = fg_name
			ORDER BY [name]
			FOR XML PATH('file'), ROOT('datafiles')) AS XML) AS data_files_status,
	reserved_space_KB + COALESCE(xml_ft_reserved_space_KB,0) AS reserved_space_KB,
	used_space_KB + COALESCE(xml_ft_used_space_KB,0) AS used_space_KB,
	/* index_space_KB = used_space_KB - data_space_KB */
		WHEN (used_space_KB + COALESCE(xml_ft_used_space_KB,0)) > data_space_KB THEN (used_space_KB + COALESCE(xml_ft_used_space_KB,0)) - data_space_KB
		ELSE 0
	END AS index_space_KB
FROM spaceused
	/* exclude system objects */
	AND system_object = 0
	AND table_name = @schemaname + '.' + @tablename
ORDER BY fg_name, table_name

When I saw the error message, I immediately thought of something related to partitioning, but when I checked the joining column’s datatypes, all were INT, as I knew they should be. So what was wrong?

I commented out all columns and added back one by one and I reached the one for the filegroup name, it failed again with the same error. I checked the sys.data_spaces, the data_space_id was INT. Again, what was wrong ? And then I checked the FILEGROUP_NAME function itself. Books Online says:

FILEGROUP_NAME ( filegroup_id )

Is the filegroup ID number for which to return the filegroup name. filegroup_id is smallint.

filegroup_id corresponds to the data_space_id column in the sys.filegroups catalog view.

That’s weird… data_space_id in sys.filegroups is INTEGER. If filegroup_id corresponds to data_space_id, why would FILEGROUP_NAME function expect it as SMALLINT ?

So let’s test it.
Scenario 1 – partitioned table, all indexes enabled.
Script runs, no errors.

Scenario 2 – partitioned table, 1 index disabled, script looks for all indexes.
Script fails.

The workaround is simple. Exclude all disabled indexes. But the root cause is still there: inconsistency in the data types between the parameter expected by this function and the source data for this parameter. And if someone would like to just list all indexes and just flag the disabled ones, will have no other choice but to script them separately. The first time the script worked because a filegroup name has been found in the sys.filegroups and the script didn’t used the FILEGROUP_NAME function. But for the disabled index, there was no filegroup retrieved and to get the data for that part of the script, it reached the

The same error is raised when you try a simpler version, but still against a partitioned table:

USE [TestFG]

	FILEGROUP_NAME(ix.[data_space_id]) AS [fg_name] 
FROM sys.indexes ix
WHERE [object_id] = OBJECT_ID('dbo.Test_FG')

BOL states the same for all SQL versions, up to 2014.

So, is this a bug by mistake or by design ?

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.