Skip to content

Instantly share code, notes, and snippets.

@ferventcoder

ferventcoder/ReduceThatDB.sql

Created June 16, 2012 12:48
Show Gist options
  • Save ferventcoder/2941270 to your computer and use it in GitHub Desktop.
Save ferventcoder/2941270 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ferventcoder revised this gist Jun 16, 2012. No changes.
  2. ferventcoder revised this gist Jun 16, 2012. 1 changed file with 14 additions and 0 deletions.
    14 changes: 14 additions & 0 deletions ReduceThatDB.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -10,6 +10,11 @@
    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.'
    @@ -72,6 +77,11 @@ GROUP BY
    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
    @@ -114,6 +124,10 @@ INNER JOIN sysindexes SI
    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
  3. ferventcoder revised this gist Jun 16, 2012. 1 changed file with 8 additions and 7 deletions.
    15 changes: 8 additions & 7 deletions ReduceThatDB.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -10,10 +10,9 @@
    DECLARE @DBName VarChar(25)
    SET @DBName = 'DBName'


    --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.'
    PRINT 'Looking at the largest tables in the database.'
    SELECT
    t.NAME AS TableName,
    i.name AS indexName,
    @@ -74,7 +73,7 @@ ORDER BY
    OBJECT_NAME(i.object_id)

    --http://ferventcoder.com/archive/2009/06/09/sql-server-2005-sql-server-2008---rebuild-or-reorganize.aspx
    Print 'Selecting Index Fragmentation in ' + @DBName + '.'
    PRINT 'Selecting Index Fragmentation in ' + @DBName + '.'
    SELECT
    DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
    ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
    @@ -115,9 +114,11 @@ INNER JOIN sysindexes SI
    ORDER BY DPS.avg_fragmentation_in_percent DESC
    GO

    DECLARE @DBName VarChar(25)
    DECLARE @DBName VarChar(25), @DBFileName VarChar(25), @DBLogFileName VarChar(25)
    SET @DBName = 'DBName'
    SET @DBFileName = @DBName
    SET @DBLogFileName = @DBFileName + '_Log'

    DBCC SHRINKFILE('DBName_Log',1)
    DBCC SHRINKFILE(@DBName,1)
    DBCC SHRINKDATABASE(@DBName,1)
    DBCC SHRINKFILE(@DBLogFileName,1)
    DBCC SHRINKFILE(@DBFileName,1)
    DBCC SHRINKDATABASE(@DBName,1)
  4. ferventcoder revised this gist Jun 16, 2012. 1 changed file with 84 additions and 6 deletions.
    90 changes: 84 additions & 6 deletions ReduceThatDB.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,17 +4,77 @@


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

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


    --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')

    Print 'Selecting Index Fragmentation 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://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
    @@ -27,19 +87,37 @@ 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

    Print 'Rebuilding indexes on every table in the database.'

    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 in the database.'
    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

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

    DBCC SHRINKFILE('DBName_Log',1)
    DBCC SHRINKFILE(@DBName,1)
    DBCC SHRINKDATABASE(@DBName,1)
    DBCC SHRINKDATABASE(@DBName,1)
  5. ferventcoder revised this gist Jun 16, 2012. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion ReduceThatDB.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,3 @@

    /*
    * Scripts to remove data you don't need here
    */
  6. ferventcoder created this gist Jun 16, 2012.
    46 changes: 46 additions & 0 deletions ReduceThatDB.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,46 @@

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


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

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

    DBCC CLEANTABLE(@DBName, 'dbo.Table1')
    DBCC CLEANTABLE(@DBName, 'dbo.Table2')

    Print 'Selecting Index Fragmentation in the database.'

    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

    Print 'Rebuilding indexes on every table in the database.'
    EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
    GO
    Print 'Reorganizing indexes on every table in the database.'
    EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"
    GO
    --EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
    --GO
    EXEC sp_updatestats
    GO

    DBCC SHRINKFILE('DBName_Log',1)
    DBCC SHRINKFILE(@DBName,1)
    DBCC SHRINKDATABASE(@DBName,1)