Return to Question

Auto growth for data file is 10MB. Increased size(new data), 455MB is estimated 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;

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.

-- Additional Information --

Auto growth for data file is 10MB. Increased size(new data), 455MB is estimated 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;

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  
--------------- ------------------ ------------------ 
DM_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 'DM01' 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.

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.