Imported database is smaller than original database

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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

 

Palomag_MSFT_0-1640258361394.png

 

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

 

Bacpac 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, 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 shrink your database you need 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 you see that your tables have to many Unused space, you need start recovering this unused space. You can do it for all tables  using the following script that reorganize and rebuilt all indexes

 

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15 

 

or  just rebuilt indexes for specific tables

 

 

ALTER INDEX ALL ON [dbo].[Table_name] REBUILD

 

 

But what happens if any of your tables don’t have any index. In this case query will not fail but will neither do anything.   For this cases you will need rebuilt the Heap table using the following syntax.

 

 

ALTER TABLE [dbo].[ Table_name] REBUILD

 

 

If you has finished the process correctly you will see that unused space for table has been decreased, and now you are ready to shrink your database. Do can do it executing this script.

 

https://github.com/yochananrachamim/AzureSQL/blob/master/Incremental%20Shrink.txt

 

Thanks

 

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.