ERROR: Could not allocate space for "tablename"


When SQL Server runs out of space this is a common error that will show up. Check the following for the cause:

  1. Make sure there is space on the disk that houses the SQL database. If not then clear out some space or move the database to another drive.
  2. Make sure the transaction log has not taken up all the room it is allocated. If the log has taken up the space then you can have it auto-grow or you can clear out the log. BE CAREFUL! The selection of options here takes research and knowledge of your database needs. If you truncate the log then you break the backup chain until you perform another backup to start the chain again.


Here is a script that works on SQL Server 2008 R2 and SQL Server 2012 that shows how much each DB table is using in rows and in disk space. This can be useful to see what tables are taking up the most space.


SELECT 

    s.Name AS SchemaName,

    t.NAME AS TableName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB, 

    SUM(a.used_pages) * 8 AS UsedSpaceKB, 

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM 

    sys.tables t

INNER JOIN 

    sys.schemas s ON s.schema_id = t.schema_id

INNER JOIN      

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255 

GROUP BY 

    t.Name, s.Name, p.Rows

ORDER BY 

    s.Name, t.Name