The following is a script that I used to help me clean up a database and reduce the size of it from 95MB down to 3MB so we could use it for a development backup. I will note that we also removed some of the data out. I shared this with a friend recently and he used this to go from 70GB to 7GB!
Update: Special Note
Please don’t run this against something that is live or performance critical. You want to do this where you are the only person connected to the database, like a restored backup of the critical database. Doing it against something live will most definitely cause issues. I can in no way be responsible for the use of this script. You should understand what you are doing before you execute these scripts.
So What Does It Do?
- It gives you a report of what tables are taking up the most space.
- It allows you to specify those tables for cleaning.
- Gives you that same report of space used up by tables after the clean.
- It rebuilds and reorganizes all indexes with reports before and after.
- It runs shrink file on the physical files (potentially unnecessary due to the next thing it does, but hey, couldn’t hurt right?!).
- It runs shrink database on the database.
The Script
Provided it shows up correctly, here is the gist:
DECLARE @DBName VarChar(25)
SET @DBName = 'DBName'
PRINT 'Looking at the largest tables in the database.'
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)
PRINT 'Cleaning the biggest offenders'
DBCC CLEANTABLE(@DBName, 'dbo.Table1')
DBCC CLEANTABLE(@DBName, 'dbo.Table2')
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)
PRINT 'Selecting Index Fragmentation in ' + @DBName + '.'
SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
PRINT 'Rebuilding indexes on every table.'
' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?
GO
PRINT 'Reorganizing indexes on every table.'
EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"
GO
PRINT 'Updating statistics'
EXEC sp_updatestats
GO
SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
GO
DECLARE @DBName VarChar(25), @DBFileName VarChar(25), @DBLogFileName VarChar(25)
SET @DBName = 'DBName'
SET @DBFileName = @DBName
SET @DBLogFileName = @DBFileName + '_Log'
DBCC SHRINKFILE(@DBLogFileName,1)
DBCC SHRINKFILE(@DBFileName,1)
DBCC SHRINKDATABASE(@DBName,1)
References
Here are some of the references in the gist:
Rob is a developer who has a passion for developing low maintenance solutions. He has been programming in .NET since the early days of 1.0. In his day job he drinks beer and tries to get creative at a digital marketing agency. Rob is a C# MVP, ASPInsider, C# Insider, an MCSD for .NET, holds a bachelor's degree in MIS from Kansas State University, is active in his local .Net User Group, a regional INETA speaker, an eagle scout, and a veteran officer of the US Army Reserve. He also presents on topics he finds interesting a few times a year.
Rob is very active in OSS and manages several OSS projects. Some of those projects include UppercuT, RoundhousE, and Chocolatey. Rob is the FerventCoder because he is very passionate about his craft.