1

I have a database with 31 tables and my largest table has about 12 columns and 750,000 records. I checked the size of all my tables using this query:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
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
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

and it says that my largest table has 147.45 MB of data:

enter image description here

When I total all of the space used from the tables it is not even 200 MB. However, when I create a backup file it is 4.5 GB.

I am not sure what is going on.

1
  • I just realized that there is a way to shrink a database using SSMS. This reduced the size of my backup file from 4.5 GB to 240 MB. I am leaving the question in case this helps someone else.
    – goxarad784
    May 5, 2021 at 2:18

2 Answers 2

2

It is interesting that shrinking reduced the backup size. Backup doesn't include unused extents. And such a massive reduction in backup size can't be explained by "lots of half-full pages" alone.

This was apparently some very special situation, in the sense that a shrink did indeed reduce the backup size to that level. One theory I have is that there are heap tables that had lots and lots of unused pages (but still being considered as used from the backup's perspective). I.e., you have some table which is being heavily inserted into and deleted from, and the delete pattern causes the free space to never being deallocated, so to speak.

If you want to aid in the value for this thread for future readers, you can run something like below to see if you have heap tables. We can't say now if potential heap tables has vast amount of free space since the shrink was performed already, but at least we would know if you have heap tables.

SELECT OBJECT_SCHEMA_NAME(OBJECT_ID), OBJECT_NAME(object_id), type_desc
FROM sys.indexes 
WHERE type_desc = 'HEAP'
AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

Perhaps LOB data not being compacted/deallocated could also explain this, but that is just me thinking out loud...

1

It is possible it was your transaction log file causing the large backup size.

Note, shrinking is generally considered a bad idea for performance and corruption risk reasons. You should investigate what recovery model your DB is using as this issue is potentially going to reoccur once your log file grows again.


Even though it is technically possible that it was the log file, I have my doubts for the following reasoning: Yes, all backups backup contains log records. But not to the size of the .ldf file, only what is needed by the recovery process performed during restore. It is theoretically possible that there was a humungous transaction (deleting billion and billions of rows) that was open while the backup was running. Shrinking the log has no relevance to this, though. The log cannot shrink past what is needed. And if it isn't needed anymore, it won't make the backup large.

So, there's a theoretical possibility that this super large delete transaction was still open when the first backup command was executed and not open when the second command was executed. IMO, this is a pretty unlikely series of events. (And the shrinking did not affect things at all, it just happened to be executed in between when the transaction was open (the first backup occasion) and when the transaction was finished (the second backup occasion).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy