Knowledge Base

FIX: Error message when you try to change a column of a table by using the ALTER TABLE statement in SQL Server 2005: "Could not allocate space"

Article ID: 916780

Article Last Modified on 4/19/2006

APPLIES TO

Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Express Edition
Microsoft SQL Server 2005 Workgroup Edition

BUG #: 430307 (SQLBUDT)

SYMPTOMS

In Microsoft SQL Server 2005, when you try to change a column of a table by using the ALTER TABLE statement, various unpredictable problems may occur. For example, the following problems may occur:

Access violations may occur.
Assertions may occur.
The SQL Server service may stop.
A database may become corrupted.
You may receive error messages that are similar to the following:

Error message 1

Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'ObjectName' in database 'DatabaseName' because the 'FileGroupName' 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.

Error message 2

Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

CAUSE

This problem occurs because the row size for the table is calculated incorrectly when SQL Server changes the column of the table.

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

913089 How to obtain the latest service pack for SQL Server 2005

WORKAROUND

To work around this problem, use one of the following methods:

If the table is not a heap table, rebuild the indexes on the table. A heap table is a table that does not have a clustered index defined.
Use the DBCC CLEANTABLE statement to reclaim the space that is used by the dropped column. For example, use the following statement.
```
DBCC CLEANTABLE ('DatabaseName','TableName')
```

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2005 Service Pack 1.

Additional query words: YukonSp1

Keywords: kbfix kbexpertiseadvanced kbsql2005engine kbsql2005tsql kbsql2005presp1fix KB916780

Microsoft KB Archive/916780

Contents