Not thinking it through - the end ( maybe )
Just to finalise the saga of the index rebuild job, we’ve submitted a replacement job which doesn’t have the failings of the current job which leaves the database without a recovery point. The current job is as follows:-
- Step 1
- disable the transaction log backup job
- Set database to “truncate log on checkpoint”
- Step 2
- within a cursor loop apply a dbcc dbreindex for each table
- Step 3
- Set database to full recovery
- Enable the transaction log backup job.
I had some discussions with the data centre and pointed out that the logic of the job steps was to quit on failure so that if the actual index rebuild failed ( step 2 ) the database was left in simple recovery with no transaction log backups ( and no recovery ). Well two weeks on and as expected the job when running successfully leaves the database with no recovery point until the next full backup, 18 hours later. On the previous week when the index rebuild step failed and the database left in simple recovery the response I had was that the database had not been switched to simple recovery, ok, so the option actually reads “trunc. log on chkpt.” but this is simple recovery !!
My change involves logic to ensure the database is returned to full recovery in the event of a step failure and the all important backup after the switch back to full recovery. The index rebuild script has been replaced with a stored procedure and a couple of tables – we carry out a dbcc showcontig, storing the results in a table so we can build up an understanding of what is fragmented, we then rebuild only those indexes which show >10 fragmentation for a clustered and >20% for a non-clustered. The actual index rebuilds are stored in another table for later analysis.
The code for the proc and tables is shown below – I have a dba database called ServerAdmin which holds the data.
So a couple of additional points in passing, after changing the database recovery model it isn’t necessary to do a full backup, a differential will suffice, assuming you’re happy using differential backups? The process thus is :-
- Step 1
- Backup the transaction log ( missing in the original job )
- Disable transaction log job
- Switch database to simple recovery
- Step 2
- Step 3
- Switch database to full recovery
- Differential backup
- Re-enable the transaction log job
In addition I’ve added a startup proc to make sure the database is in full recovery, this is just in case the cluster should failover during the index rebuild.
A stored procedure placed in master can be made to execute on service start by issuing the following command against it
exec dbo.sp_procoption N'sp_myprocedure', N'startup', N'true'
This is a useful feature for those not in the know, I usually have a procedure which sends a smtp mail set this way, so if the service restarts there is an immediate mail alert, useful if you want to know if your cluster has failed over for example.
Other uses can be to set memory allocations to instances, thus making sure instances do not suffer starvation. I applied this technique to a development server where the developers had sysadmin rights and were prone to ( well tried to ) making changes to the memory for the various instances, I admit the procedure was very well hidden and encrypted but it did assist in making sure one instance could not grab all the memory.
This is the code for the proc and support tables.
Note that this rebuilds all indexes with 100% fillfactor, this is intentional as I don’t believe in allocating fill factors unless you know it will help reduce fragmentation , I personally dislike the practice of allocating a 90% or 80% fill factor as a blanket value – just makes the database bigger and may increase i/o though increased scans.
So in the event of creating individual fill factors the sysnatx of the dbcc would need to be changed.
Apologies for loss of some of the formatting.
Use ServerAdmin
go
--
-- create tables for proc
--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FragList_mydb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FragList_mydb]
GO
CREATE TABLE [dbo].[FragList_mydb] (
[ObjectName] [char] (255) NULL ,
[ObjectId] [int] NULL ,
[IndexName] [char] (255) NULL ,
[IndexId] [int] NULL ,
[Level] [int] NULL ,
[CountPages] [int] NULL ,
[CountRows] [int] NULL ,
[MinRecSize] [int] NULL ,
[MaxRecSize] [int] NULL ,
[AvgRecSize] [int] NULL ,
[ForRecCount] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AvgFreeBytes] [int] NULL ,
[AvgPageDensity] [int] NULL ,
[ScanDensity] [decimal](18, 0) NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFrag] [decimal](18, 0) NULL ,
[ExtentFrag] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ResultTable_mydb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ResultTable_mydb]
GO
CREATE TABLE [dbo].[ResultTable_mydb] (
[Command] [varchar] (1000) NOT NULL ,
[TheTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
--
-- create the proc in the user database to have the index rebuild
--
Use mydb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbasp_IndexBuild_mydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dbasp_IndexBuild_mydb]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc dbo.dbasp_IndexBuild_mydb
@p1 int=0
-- =============================================================
-- Stored Procedure: dbasp_IndexBuild_mydb
-- Written by: (c)colin leversuch-roberts
-- www.kelemconsulting.co.uk
--
-- Purpose: Procedure to facilitate selective index rebuilds for mydb Database
-- Required to provide checkpoints to clear transaction log during the rebuild
-- as tran log space is limited
--
-- System: Calling User database ( must exist within the database to be defragged )
--
-- Input Paramters: @p1 int - calling code
--
-- Output Parameters: None
--
-- Usage: EXEC dbo.dbasp_IndexBuild_mydb 38547
--
-- Called By: SQL Agent
--
-- Notes: Calling code stops accidental execution of stored procedure
-- This proc execute a dbcc showcontig which is intrusive
-- dbcc dbreindex only updates stats when applied to a table so we must
-- always make sure we also update stats in a seperate job
--
-- VERSION HISTORY
-- Version No Date Description
-- 1 22-August-2002 Initial Release
-- 2 23-May-2003 Move to ServerAdmin
-- 3 25-Nov-2003 Code rewrite. used raiserror for errors clr
-- 4 14th dec 2006 mydb version which requires t log truncates
--
-- =============================================================
as
set nocount on
IF @p1<>38547
BEGIN
raiserror('error running proc',16,1 )
Return(-1)
END
--endif
--
-- Truncate table prior to population
--
Truncate table ServerAdmin.dbo.FragList_mydb
Truncate table ServerAdmin.dbo.ResultTable_mydb
--
-- Populate the table
--
INSERT INTO ServerAdmin.dbo.FragList_mydb([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [CountPages], [CountRows], [MinRecSize], [MaxRecSize], [AvgRecSize], [ForRecCount], [Extents], [ExtentSwitches], [AvgFreeBytes], [AvgPageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFrag], [ExtentFrag])
EXEC(' DBCC SHOWCONTIG with all_indexes,TABLERESULTS')
--
-- remove system tables
--
delete from ServerAdmin.dbo.FragList_mydb where objectname like 'sys%'
--
-- Create a list of fragmented clustered indexes.
-- Choose fragmentation of >10%
-- choose tables greater than 8 pages in size ( 1 extent )
-- Defrag the largest tables first
-- As the clustered index rebuild will rebuild all secondary indexes we only need
-- to rebuild secondary indexes where we haven't rebuilt the clustered index
--
declare @Clustered table
(
ObjectName varchar (255) not NULL ,
ObjectId int not NULL ,
IndexName varchar (255) not NULL ,
CountPages int not NULL,
Numkey int not null identity(1,1)
)
--
insert into @Clustered (ObjectName,ObjectID,IndexName,CountPages)
select ObjectName,ObjectID,IndexName,CountPages from ServerAdmin.dbo.FragList_mydb where indexid=1 and scandensity<90
and countpages>8
order by CountPages desc
--
-- now get the non clustered indexes
--
declare @NonClustered table
(
ObjectName varchar (255) not NULL ,
ObjectId int not NULL ,
IndexName varchar (255) not NULL ,
CountPages int not NULL,
Numkey int not null identity(1,1)
)
--
-- The page size was tricky to define for effective rebuilds
-- a 3 page index did not rebuild very well, but a 7 page did
--
insert into @NonClustered (ObjectName,ObjectID,IndexName,CountPages)
select ObjectName,ObjectID,IndexName,CountPages from ServerAdmin.dbo.FragList_mydb where indexid>1 and indexid<255 and scandensity<80
and countpages>3
and objectID not in ( select objectid from @Clustered )
order by countPages desc
/*
Now use cursors to rebuild the indexes
I don't like cursors and would usually use a while loop
however a while does tend to bind everything into a transaction
and I'd prefer that not to happen
--
A result table time stamped will be populated so we can see what was actually rebuilt
*/
--
Declare @ResultTable table ( Command varchar(1000) not null, TheTime datetime not null default getdate())
--
--
Declare @tablename varchar(255),@IndexName varchar(255),@CmdStr nvarchar(1000)
-- populate cursor for clustered indexes
DECLARE IndexRebuild CURSOR FOR SELECT ObjectName FROM @Clustered order by Numkey
-- Open the cursor
OPEN IndexRebuild
-- loop through the indexes
FETCH NEXT FROM IndexRebuild INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
set @CmdStr = 'dbcc dbreindex('+rtrim(@tablename)+','+''''+''''+','+'100) WITH NO_INFOMSGS '
--print @cmdstr
exec (@cmdstr)
insert into @ResultTable(Command) values(@cmdstr)
--
-- issue a checkpoint to empty transaction log - Modification for mydb
--
checkpoint
FETCH NEXT FROM IndexRebuild INTO @tablename
END
-- Close and deallocate the cursor
CLOSE IndexRebuild
DEALLOCATE IndexRebuild
--
-- non clustered indexes ( shouldn't need a checkpoint here as secondary indexes are typically small )
--
DECLARE IndexRebuild CURSOR FOR SELECT ObjectName,IndexName FROM @NonClustered order by Numkey
-- Open the cursor
OPEN IndexRebuild
-- loop through the indexes
FETCH NEXT FROM IndexRebuild INTO @tablename,@IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
set @CmdStr = 'dbcc dbreindex('+rtrim(@tablename)+','+rtrim(@IndexName)+','+'100) WITH NO_INFOMSGS '
--print @cmdstr
exec (@cmdstr)
insert into @ResultTable(Command) values(@cmdstr)
FETCH NEXT FROM IndexRebuild INTO @tablename,@IndexName
END
-- Close and deallocate the cursor
CLOSE IndexRebuild
DEALLOCATE IndexRebuild
insert into ServerAdmin.dbo.ResultTable_mydb
select * from @ResultTable
--
-- add a final checkpoint here
--
checkpoint
-- endproc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--