Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Super D to the B to the A - AKA Script for reducing the size of a database

, 23 Jan 2013 Apache
Rate this:
Please Sign up or sign in to vote.
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:

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0

Share

About the Author

ferventcoder
Web Developer
United States United States
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.
Follow on   Twitter   Google+

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 23 Jan 2013
Article Copyright 2013 by ferventcoder
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid