Handling an over sized db to save space(SQL Server)

Sometimes having too many unwanted backup and temporary tables will increase the space by huge volumes so it is a best practice to often clean them and keep it tidy. This will improve the performance as well as reduce memory issues.

The below stack question contains a query which can be used to identify the space consuming tables in SQL Server. I have attached the question and the query to be executed.

https://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database/2094454

SELECT 

    t.name AS TableName,

    i.name as indexName,

    sum(p.rows) as RowCounts,

    sum(a.total_pages) as TotalPages, 

    sum(a.used_pages) as UsedPages, 

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM 

    sys.tables t

INNER JOIN      

    sys.indexes i ON t.object_id = i.object_id

INNER JOIN 

    sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

    t.name NOT LIKE 'dt%' AND

    i.object_id > 255 AND  

    i.index_id <= 1

GROUP BY 

    t.name, i.object_id, i.index_id, i.name 

ORDER BY 

    object_name(i.object_id)

Execute the above query and identify the space consuming unwanted tables and drop them. even you drop them the consumed space will not be released to the windows and to do that we need to use shrink db task from UI.

Then select that option and click OK to shrink the db and release the space to windows.