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

azure azure-sql-database shrink
Pekka Ylenius picture Pekka Ylenius · Oct 30, 2013 · Viewed 13k times · Source

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?

Answer

Pekka Ylenius picture Pekka Ylenius · Oct 31, 2013

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.

Related questions

Optimal way to concatenate/aggregate strings

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR …

Read More
Azure SQL Database "DTU percentage" metric

With the new Azure SQL Database tier structure, it seems important to monitor your database "DTU" usage to know whether to upgrade or downgrade to another tier. When reading Azure SQL Database Service Tiers and Performance Levels, it only talks …

Read More
How to re-create database for Entity Framework?

I have got into a bad state with my ASP.Net MVC 5 project, using Code-First Entity Framework. I don't care about losing data, I just want to be able to start fresh, recreate the database and start using Code-First migrations. …

Read More