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)