CODEWITHSUNDEEP
azureazure-sql-databaseshrink
Pekka Ylenius
Pekka Ylenius

Reputation: 498

How to shrink Azure SQL server DB (18MB of data charged for 5GB of server space already)

I have problem with Azure SQL DB. Bacpack export of DB is only 18MB but charged DB size of server exceeds 5GB already.

Is there any way to see actual size of data?

Is there any way to move DB to simple recovery model? Or is there any other way to shrink log files?

Or should I just drop Database and restore from backup?

Upvotes: 14

Views: 13878

Answers (2)

Pekka Ylenius
Pekka Ylenius

Reputation: 498

Problem was caused by defragmented indexes.

You can find good scripts for fixing those from here: http://blogs.msdn.com/b/dilkushp/archive/2013/07/28/fragmentation-in-sql-azure.aspx

After running scripts (and 24h) size of DB went back to 300MB.

Upvotes: 21

Eoin Campbell
Eoin Campbell

Reputation: 44268

The bacpac file is going to be significantly smaller than the DB as it's a compressed version of the data and I believe it strips out things like index content and only stores index definitions which are reindexed on restore so one shouldn't be indicative of the other.

For example, I have a database on SQL Azure configured as a 10GB Premium DB, which is currently using 2.7GB, which BACPACs to about 300MB

What kind of database have you configured ? What Edition, Size and Usage settings are you currently being shown.

** Edit ** Image wasn't loading so here's the external link - http://i.snag.gy/JfsPk.jpg

The next thing to check is the size breakdown in the database by table/object. Connect to your Azure environment with Management Studio and run the following query. which will give a table breakdown of the database with sizes in MB.

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024
from    
      sys.dm_db_partition_stats, sys.objects
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id

group by sys.objects.name

Upvotes: 3

Related Questions