Rebuild database

While still in design and development, a database goes through many changes, tables and columns are added and removed, records are added and deleted, sometimes performance tests are performed, a data structure goes through sanity testing, indexes get fragmented, etc'.


this leaves a lot of junk in the database files, sometimes this space is reclaimed by the SQL engine and sometimes you need to ask for it specifically, that is the purpose of this stored procedure.


I've used portions of it over the years but only this time I've decided to collect all of it into a single stored procedure, the dynamic nature of the current project I'm working on makes me execute this once in a while. I thought I'd share this with you, please be aware that this should not be executed in a production environment, you must read and understand what its doing before you execute it, it might cause some serious data loss in specific cases.


To help you avoid making that mistake, I've commented all the contents of the stored procedure and put a return in the beginning of it. 

-- =============================================
-- Author:  Dror Gluska
-- Create date: 2012-03-05
-- Description: Performs cleanup/rebuild on all indexes, freeing space and accelerating queries
-- =============================================
create PROCEDURE [dbo].[usp_DBCCCleanup]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 SET NOCOUNT ON;

    return;

----rebuild tables

--print 'Rebuilding tables...'

--declare @rebuildtables table(name nvarchar(255), cmd nvarchar(max));

--insert into @rebuildtables
--SELECT  o.[name],'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD ;'
--FROM sys.objects AS o WITH (NOLOCK)
--INNER JOIN sys.indexes AS i WITH (NOLOCK)
--ON o.[object_id] = i.[object_id]
--INNER JOIN sys.schemas AS s WITH (NOLOCK)
--ON o.[schema_id] = s.[schema_id]
--INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
--ON i.[object_id] = ps.[object_id] AND ps.[index_id] = i.[index_id]
--WHERE o.[type] = 'U' ORDER BY ps.[reserved_page_count]

--declare c_rebuild cursor for select name, cmd from @rebuildtables

--open c_rebuild

--declare @tname nvarchar(255), @tcmd nvarchar(max);

--fetch next from c_rebuild into @tname, @tcmd
--while (@@fetch_status <> -1)
--begin
-- print 'Rebuilding ' + @tname
-- exec sp_executesql @tcmd
 
-- fetch next from c_rebuild into @tname, @tcmd
--end
--close c_rebuild
--deallocate c_rebuild


--print 'Done rebuilding tables'

--print 'Rebuilding indexes...'
----taken from http://technet.microsoft.com/en-us/library/bb838727(v=office.12).aspx

--DECLARE @objectid int;
--DECLARE @indexid int;
--DECLARE @partitioncount bigint;
--DECLARE @schemaname nvarchar(130);
--DECLARE @objectname nvarchar(130);
--DECLARE @indexname nvarchar(130);
--DECLARE @partitionnum bigint;
--DECLARE @partitions bigint;
--DECLARE @frag float;
--DECLARE @command nvarchar(4000);
--DECLARE @dbid smallint;

---- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
---- and convert object and index IDs to names.

--SET @dbid = DB_ID();

--SELECT
--    [object_id] AS objectid,
--    index_id AS indexid,
--    partition_number AS partitionnum,
--    avg_fragmentation_in_percent AS frag, page_count
--INTO #work_to_do
--FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
--WHERE
----avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation
----AND
--index_id > 0 -- Ignore heaps
----AND page_count > 25; -- Ignore small tables
---- Declare the cursor for the list of partitions to be processed.
--DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
---- Open the cursor.
--OPEN partitions;
---- Loop through the partitions.
--WHILE (1=1)
--BEGIN
--FETCH NEXT
--FROM partitions
--INTO @objectid, @indexid, @partitionnum, @frag;
--IF @@FETCH_STATUS < 0 BREAK;
--SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
--FROM sys.objects AS o
--JOIN sys.schemas as s ON s.schema_id = o.schema_id
--WHERE o.object_id = @objectid;
--SELECT @indexname = QUOTENAME(name)
--FROM sys.indexes
--WHERE object_id = @objectid AND index_id = @indexid;
--SELECT @partitioncount = count (*)
--FROM sys.partitions
--WHERE object_id = @objectid AND index_id = @indexid;
---- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
--IF @frag < 30.0
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
--IF @frag >= 30.0
--SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
--IF @partitioncount > 1
--SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
--print 'Rebuilding/Reogranizing index ' + @indexname
--EXEC (@command);
--PRINT N'Executed: ' + @command;
--END
---- Close and deallocate the cursor.
--CLOSE partitions;
--DEALLOCATE partitions;
---- Drop the temporary table.
--DROP TABLE #work_to_do;
--print 'Updating usage'
--DBCC UPDATEUSAGE (0)
--print 'Shrinking database'
--declare @shcmd nvarchar(255) = 'DBCC SHRINKDATABASE (' + DB_NAME() + ',10)'
--exec (@shcmd)
 
END

Tags: sql