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.
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
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
or just rebuilt indexes for specific tables
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.
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