Menu
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?
875
In Azure SQL Database, to shrink files you can use either DBCC SHRINKDATABASE or DBCC SHRINKFILE commands: DBCC SHRINKDATABASE shrinks all data and log files in a database using a single command. The command shrinks one data file at a time, which can take a long time for larger databases.
To shrink a data or log file. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases and then right-click the database that you want to shrink. Point to Tasks, point to Shrink, and then select Files.
When data space used reaches the maximum data size limit, either at the database level or at the elastic pool level, inserts and updates that increase data size fail and clients receive an error message. SELECT and DELETE statements remain unaffected.
This is true that shrinking a database is not recommended. You can understand it like this when you shrink the database then it leads to increase in fragmentation now to reduce the fragmentation you try to rebuilt the index which will eventually lead to increase in your database size.
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.
143
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
