Can not add data file to the database with fixed size files

Can not add data file to the database with fixed size files by SGolovko

Closed

as By Design Help for as By Design

Type:	Bug
ID:	524945
Opened:	1/14/2010 8:09:59 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

SQL Server version - 9.0.4266.
We have database which has 34 data files 8GB each. When the last file was half full I tried to add new database file and get and error:

Msg 1105, Level 17, State 2, Line 8
Could not allocate space for object 'dbo.tst_Large' in database 'TestAddFile' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Maximum number of data files for the database is 32,767. I tried to add 35th file. We did not have this problem before we needed 35th file (I was able to add data files).

Workaround:
1) Enable file growth for the first data file
2) Add new file
3) Disable file growth for the first data file

Details (expand)

English

SQL Server 2005 SP3

SQL Engine

Win2003 Enterprise Server (SP2)

US English

1) CREATE DATABASE WITH 34 FILES
Each file 10 Mb initial size, 10 mb max size, no growth ( NAME = N'TestAddFile_dat', FILENAME = N'H:\MSSQL.1\MSSQL\Data\TestAddFile_1.mdf' , SIZE = 10240KB , MAXSIZE = 10240KB, FILEGROWTH = 0),

2) TRY TO ADD DATA FILE WHEN DATABASE IS EMPTY (THIS SHOULD WORK)
ALTER DATABASE [TestAddFile]
ADD FILE ( NAME = N'TestAddFile_dat35',
FILENAME = N'H:\MSSQL.1\MSSQL\Data\TestAddFile_35.ndf' ,
SIZE = 1MB , MAXSIZE = 10MB , FILEGROWTH = 1MB )
TO FILEGROUP [PRIMARY]
3) CREATE TEST TABLE AND START POPULATING UNTIL DATABASE IS FULL (IN OUR CASE THERE WAS 2 GB OF FREE SPACE ON FILE 34, DATABASE WAS NOT FULL)
USE [TestAddFile]
GO
SET NOCOUNT ON
CREATE TABLE dbo.tst_Large (Long1 CHAR (4000), Long2 CHAR(4000) )
DECLARE @n INT
SELECT @n = 1
WHILE @n < 1000000
BEGIN
INSERT INTO dbo.tst_Large SELECT REPLICATE ('n',4000),REPLICATE ('m',4000)
SELECT @n = @n+1
END

4) ADD ANOTHER DATA FILE
ALTER DATABASE [TestAddFile]
ADD FILE ( NAME = N'TestAddFile_dat36',
FILENAME = N'H:\MSSQL.1\MSSQL\Data\TestAddFile_36.ndf' ,
SIZE = 1MB , MAXSIZE = 10MB , FILEGROWTH = 1MB )
TO FILEGROUP [PRIMARY]
GO

--RESULT - FILE CAN NOT BE CREATED

Can not add data file.
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'sys.sysfiles1'.'sysfiles1' in database 'TestAddFile' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Workaround:
UPDATE DATABASE - ENABLE FILE GROWTH FOR THE PRIMARY FILE
ALTER DATABASE [TestAddFile]
MODIFY FILE ( NAME = N'TestAddFile_dat', MAXSIZE = 21504KB , FILEGROWTH = 1024KB )
GO

Add data file now - file created.

Should be able to add data file without additional step as it worked with the same configuration before.

X64

File Name	Submitted By	Submitted On	File Size
FileAddError.sql (restricted)	SGolovko	1/14/2010	-

The issue is when we are adding the first 34 files, the page for sys.sysfiles1 has space to add more entries so the add file would succeed. When adding the 35th, that page is full and we need to allocate a new page for the system table but we ran out of space. This is by-design.

Based on the above explanation, I am closing this item. Thank you for your feedback