Skip to content

@ferventcoder /ReduceThatDB.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
D to the B to the A - Reducing the size of a SQL Server database
/*
* 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)
@ferventcoder

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

@ferventcoder

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

@ferventcoder

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/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.