The database is smaller than the original database.

The database is smaller than the original database

SQL Database, there are workload patterns where the allocation of underlying data files for databases can become larger than the number of used data pages. This condition can occur when the space used increases and data is subsequently deleted. The reason is that file space allocated is not automatically reclaimed when data is deleted.

If you export your database to a file, and then you import it, it could be possible that the target database will be smaller than the original one, but if you restore any of the available backups, the allocated size will be the same as the original database.??If you see this do not go into a panic, there is an explanation for this.

The file contains Schema and data, while.bak file contains a page-by-page copy of the database, and SQL Server database contains not only data pages, but there are also pages with indexes that can be large.

?If you are in front of this situation, you could think to shrink your original database to reduce its allocated space. But before shrinking your database you need to review the space used per table

SELECT

???t.NAME AS TableName,

???s.Name AS SchemaName,

???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.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.is_ms_shipped = 0

???AND i.OBJECT_ID > 255

GROUP BY

???t.Name, s.Name, p.Rows

ORDER BY

???t.Name

If we see that your tables have too much Unused space, you need to start recovering this unused space. we can do it for all tables by shrinking.