Could not allocate space
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:
- 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.
- 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.
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
sys.schemas s ON s.schema_id = t.schema_id
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
t.NAME NOT LIKE 'dt%' -- filter out system tables for diagramming
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows