D to the B to the A - Reducing the size of a SQL Server database

ReduceThatDB.sql

/*
 * 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.'
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
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) 

Shrank a 95MB database backup down to a 3MB database backup. Yeah boyeee! :D

When I left college I was originally going to be a DBA. Then I was steered into development. I believe database knowledge helps steer my strengths as a developer, and definitely makes for a stronger product in RoundhousE. :D

One of the best folks to follow out there with uber DBA skillz is Bill "The Wizard of Graz" Graziano. One of the most influential folks I know in DBA land. http://www.billgraziano.com/