I checked SQL Server database size by sp_spaceused. Even though there seems to be enough unallocated space, some database issued the file expanding error.
-- sp_spaceused result
use db_01;
EXEC sp_spaceused;
database_name database_size unallocated space
--------------- ------------------ ------------------
db_01 868463.31 MB 55504.24 MB
reserved data index_size unused
------------- ------------- ------------- -------------
760088904 KB 435604016 KB 321346232 KB 3138656 KB
EXEC sp_spaceused N'dbo.target_talbe';
name rows reserved data index_size unused
-------------------------------------------------------------------------------------------
dbo.target_talbe 744509531 386101616 KB 170471256 KB 215097944 KB 532416 KB
Error Message
2021-12-27 00:38:49.04 spid52 Could not allocate a new page for database 'db_01' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
This error was cleared when I added the new data file (ndf) or expanded the max size of datafile.
I'm wondering why DB unallocated space couldn't be reused. Target table had 519MB as unused space and new data was about 455MB, I know I should consider the data size by page. But 'Unallocated space' in DB can not be reusable space for other table's new data?
If so, is there any better idea to check the exact table size and avoid any file expanding error?
Any advice would be appreciated. Thank you.
-- Additional Information --
Auto growth for each data file is 10MB. Increased size(new data), 455MB is calculated 1 row size * row count of new data, 0.5KB * 910,455 rows. 1 row size was roughly estimated, Total_Reserved_kb(target table size) divided total RowsCount, by following script;
SELECT
t.object_id,
OBJECT_NAME(t.object_id) ObjectName,
sum(u.total_pages) * 8 Total_Reserved_kb,
sum(u.used_pages) * 8 Used_Space_kb,
u.type_desc,
max(p.rows) RowsCount
FROM
sys.allocation_units u
JOIN sys.partitions p on u.container_id = p.hobt_id
JOIN sys.tables t on p.object_id = t.object_id
GROUP BY
t.object_id,
OBJECT_NAME(t.object_id),
u.type_desc
ORDER BY
Used_Space_kb desc,
ObjectName;
object_id ObjectName Total_Reserved_kb Used_Space_kb type_desc RowsCount
--------------------------------------------------------------------------------------
635149308 target_talbe 386411248 385871760 IN_ROW_DATA 745087178
If there is any misunderstanding, please let me know.
