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

Question

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?

Olgaolguin

Answer 1

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.

Olgaolguin

That link is broken. Funny, several questions on SO point to the link. – Motherland

Answer 2

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

Fenian

I was using max 5GB Web version first. But I had to go for max 10GB Business for sometime. Now after regenerating indexes I'm back in less than 1GB. – Olgaolguin