Today my team woke me up with a Good Morning call. Here is the conversation
Team: Pradeep, Primary Filegroup is full
Me: Ok, add one more data file (acting to be be fully awake and in control of things)
Team: No, we were adding space to Primary File Group and it fails saying it is full !
Me: Ok, I am logging in. (Nice excuse to wake up fully and realize where I am !)
Ok, my day had already started. Caught some more sleep while the laptop was still warming up. This incident was quite interesting.
The DBA was trying to add a new data file on the PRIMARY filegroup on an instance running SQL Server 2000. This database was running out of space and lot of email communication and a lot of justification, business agreed to “provide” some space for this database. ThePRIMARY filegroup had ~ 1GB free space before adding space. It had 6 data files. Looks like the instance did not like the number 7. Whenever the DBA issued theALTER DATABASE command, it said
*********************
Sever: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object ‘SYSTEM table id: 8)’ in database ‘databasename’ because the ‘PRIMARY’ filegroup is full.
*********************
select object_name(8) told me that the object 8 was sysfiles1. One would feel, the primary file group has 1 GB of free space available and it is failing to insert one row into sysfiles1 table? The answer is yes, it failed to insert a single row because of insufficient space in the data file.
The system objects are always located on the first file in thePRIMARY filegroup. I looked at the first file of the PRIMARY filegroup and its size was 73 MB! The used space for this datafile The next setting I looked at was theAuto Growth. It was set to 10 MB and the MAXSIZE for that file was set at 80 MB. From this perspective as well it has 7 more MB to grow. Then where is the catch? The drive on which this file was located had only 2 MB free space ! I wish to meet the person who did this setting at least once in my lifetime.
Here is how it lead to the resolution. The data file was completely filled out and needed to auto grow to insert a single row. But the size on disk was 2 MB and data file would grow 10 MB at a time. We had 2 options to resolve this. Either increase the current size of the data file by 1 MB or reduce the auto grow setting to 1 MB. I chose the first option for no obvious reasons and increased the allocated size for that data file to 74 MB.
After this issued the ALTER DATABASE command and we were able to add the new data file to the database.
