Microsoft KB Archive/229726

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

Article ID: 229726

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q229726



BUG #: 55290 (SQLBUG_70)



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='\tempdb.mdf') go ALTER DATABASE TEMPDB MODIFY FILE (NAME='TEMPLOG', FILENAME='\templog.ldf') go 



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



MORE INFORMATION
SQL Server 7.0 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.

Keywords: kbbug kbpending KB229726

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.