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
(
SELECT DISTINCT
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,
(SELECT SUM(
CASE
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
END
) * 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
WHERE
/* Only get clustered tables / heaps */
p.[index_id] <= 1
)
SELECT
fg_name,
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],
[physical_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,
table_name,
num_of_indexes,
system_object,
ps_name,
pf_name,
num_rows,
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,
data_space_KB,
/* index_space_KB = used_space_KB - data_space_KB */
CASE
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
WHERE
1=1
/* 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:
Syntax:
FILEGROUP_NAME ( filegroup_id )filegroup_id
Is the filegroup ID number for which to return the filegroup name. filegroup_id is smallint.Remarks:
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]
SELECT
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 ?
SQL-PLAY 