Super D to the B to the A - AKA Script for Reducing the Size of a Database

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:

/*
 * Scripts to remove data you don't need here  
 */

/*
 * Now let's clean that DB up!
 */

DECLARE @DBName VarChar(25)
SET @DBName = 'DBName'

/*
 * Start with DBCC CLEANTABLE on the biggest offenders
 */

--http://stackoverflow.com/questions/3927231/how-can-you-tell-what-
--    tables-are-taking-up-the-most-space-in-a-sql-server-2005-d
--http://stackoverflow.com/a/3927275/18475
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) 

 --http://weblogs.sqlteam.com/joew/archive/2008/01/14/60456.aspx
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) 

/*
 * Fix the Index Fragmentation and reduce the number of pages 
   you are using (Let's rebuild and reorg those indexes)
 */

--http://ferventcoder.com/archive/2009/06/09/sql-server-2005-sql-server-2008---rebuild-or-reorganize.aspx 
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 --N'LIMITED') 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
--EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
--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 --N'LIMITED') 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

/*
 * Now to really compact it down. It's likely that SHRINKDATABASE will do the 
   work of SHRINKFILE rendering it unnecessary but it can't hurt right? Am I right?!
 */

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:

• http://stackoverflow.com/a/3927275/18475
• http://stackoverflow.com/questions/3927231/how-can-you-tell-what-tables-are-taking-up-the-most-space-in-a-sql-server-2005-d
• http://ferventcoder.com/archive/2009/06/09/sql-server-2005-sql-server-2008---rebuild-or-reorganize.aspx 
• http://weblogs.sqlteam.com/joew/archive/2008/01/14/60456.aspx