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)