BUG: A Foreign Key Table with Data in Tempdb Creates Error 1105

• Microsoft SQL Server version 7.0

SYMPTOMS

Creating a composite foreign key on a table that already contains data may fail with the following error:

Error: 1105, Severity: 17, State: 2
Could not allocate space for object '(SYSTEM table id: -669903346)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.

CAUSE

When you create a composite foreign key, make sure the entire table the foreign key is being placed on is loaded into tempdb. An error 1105 will occur if tempdb is:

• Set to a fixed size that accommodates the entire table.



• Ready to grow automatically as the table changes size.



• Set up to accommodate more space on the drive than is actually available.

WORKAROUND

To work around this problem, you can either:

• Use the NOCHECK option when creating the foreign key.
  
  -or-



• Ensure that tempdb has enough space to grow, at a minimum, to the size of the table on which the foreign key is being placed. If tempdb requires more drive space, use the following script to move tempdb and its log to a drive with sufficient space:

  
  ALTER DATABASE tempdb
  MODIFY FILE
  (NAME='TEMPDEV',
  FILENAME='<new drive letter and path>\tempdb.mdf')
  go
  ALTER DATABASE TEMPDB
  MODIFY FILE
  (NAME='TEMPLOG',
  FILENAME='<new drive letter and path>\templog.ldf')
  go 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

SQL Server 6.5 checks a portion of data at a time. It frees tempdb space between each check. Therefore, tempdb never approaches the size of the foreign key table.

Additional query words:

Keywords : kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug