Shrink a SQL Database
A couple of weeks ago, I had an Azure SQL Database size explode. You can’t run reports that come with SQL that tell you table sizes on an Azure SQL Database, so you have to use a query. It ended up being a log table (yes, it’s a log table and not 2010 anymore) that needed truncated.
List the size of all the tables in the SQL Database
select o.name, max(s.row_count) AS 'Rows', sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as 'GB', (8 * 1024 * sum(s.reserved_page_count)) / (max(s.row_count)) as 'Bytes/Row' from sys.dm_db_partition_stats s, sys.objects o where o.object_id = s.object_id group by o.name having max(s.row_count) > 0 order by GB DESC
After that, I needed to shrink the database. I used the queries below to determine the logical file names for the DBCC SHRINKFILE.
See the files
-- Get at list of files -- Note: Name column is the logical name needed for DBCC SHRINKFILE SELECT * FROM sys.database_files DBCC SHRINKFILE (data_0, 10)