SQL SERVER – Incorrect Error: Unable to Add Filegroup – A Severe Error Occurred on the Current Command

One of my customers recently reached out to me to help them out with a strange error which they were facing related to Add FileGroup. After carefully looking at the issues, we figured out that real issue was much deeper and very different.

The Problem

Hi Pinal,
We are having a huge database where we are using partitioning. Due to some reason, we are not able to add new filegroup. Simple add command is failing with an error.

Msg 0, Level 11, State 0, Line 10
A severe error occurred on the current command.  The results, if any, should be discarded.

Can you please help us?

Since this was a very interesting issue, I gave them my offer and terms and conditions. Once they accepted, we joined GoToMeeting to look into the issue.

They showed me the error. I asked them how many file groups they have and they answered that they should have around 3000 file groups. I have asked them to run below command to double check

SELECT count(*)
FROM sys.filegroups

And they were surprised with the number which came. It was 32767.

When I looked for documentation Maximum Capacity Specifications for SQL Server, I found the same magical number under “Filegroups per database”

So now, we knew that SQL is not giving us the right error message. After many years I have seen such failure in SQL Server. Soon my client realized that their filegroup cleanup job has been failing.

Here is the script to reproduce the error.

CREATE DATABASE FileGroupDB
GO
DECLARE @i INT =1
DECLARE @str VARCHAR(100)
WHILE (@i <= 32766)
BEGIN
	SELECT @str = 'ALTER DATABASE [FileGroupDB] ADD FILEGROUP [FG'+CONVERT(varchar(10),@i)+']'
	EXEC(@str)
	SET @i = @i+1
END
GO
ALTER DATABASE [FileGroupDB] ADD FILEGROUP SQLAuth
GO

At the last command, we would see below error

Cause

The maximum amount of filegroups per database is 32,767 which they have reached.

Resolution

They realized that SQL is giving an incorrect error and after removing unused file groups, they were able to do their regular partitioning.

Have you seen such errors which are incorrect in SQL Server? Please share it via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)