Technical Article

Reindex Procedure with a Twist (or two) the SQL

,

This script is a 2012 update to Reindex Procedure with a Twist (or two)

We’ve all seen reindex procedures that rebuild all indexes in a database or all indexes in a database which have less than a specified ScanDensity. This one goes a few steps further. First, the DBA can specify a ScanDensity_Threshold (a min value for ScanDensity), plus a Limit (qty or %). If you specify a ScanDensity_Threshold of 80, and a Limit of 50%, then only the worst 50% of all indexes in the database with a ScanDensity < 80 will be rebuilt. It determines the “worst” n% of all indexes by calculating a Reindex_Factor based on ScanDensity and PageCount. An index with a ScanDensity of 70 and 2000 pages will be reindexed before an index with a ScanDensity of 25 and 2 pages. After it finishes, you can query the DBA_Index_History table to see which indexes were rebuilt and why. The DBA has the option to specify a FillFactor_Age_Limit. If an index is rebuilt a second time within the FillFactor_Age_Limit timeframe, the FillFactor will be reduced by 5, to a minimum of 50. (If this is not desired, simply set FillFactor_Age_Limit = 0).

This procedure is written to take advantage of a “DBA” database which I have on each of my SQL Servers. If you store your maintenance procedures in the master database, you will need to take this into account, but I highly encourage you to move them into a “DBA” database. It greatly simplifies management! There is much documentation in the code. Please examine the procedure carefully understand how it works before you implement it. This version of the procedure has been tested in SQL 2012. With that, I hope you find this procedure as useful as I have.  Many thanks to hoo-t for posting the original script

USE [master]
GO
/****** Object:  Database [DBA]    Script Date: 01/29/2008 10:36:04 ******/CREATE DATABASE [DBA] 
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DBA', @new_cmptlevel=110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DBA].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DBA] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DBA] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DBA] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DBA] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DBA] SET ARITHABORT OFF
GO
ALTER DATABASE [DBA] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DBA] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DBA] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DBA] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DBA] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DBA] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DBA] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DBA] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DBA] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DBA] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DBA] SET  DISABLE_BROKER
GO
ALTER DATABASE [DBA] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DBA] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DBA] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DBA] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DBA] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DBA] SET  READ_WRITE
GO
ALTER DATABASE [DBA] SET RECOVERY FULL
GO
ALTER DATABASE [DBA] SET  MULTI_USER
GO
ALTER DATABASE [DBA] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [DBA] SET DB_CHAINING OFF
GO
USE [DBA]
GO
/****** Object:  Table [dbo].[DBA_dbReindex_showcontig]    Script Date: 01/29/2008 10:36:04 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBA_dbReindex_showcontig](
[DBName] [char](128) NULL,
[ObjectName] [char](128) NULL,
[ObjectId] [int] NULL,
[IndexName] [char](128) NULL,
[IndexId] [int] NULL,
[Lvl] [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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DBA_Index_Stats]    Script Date: 01/29/2008 10:36:04 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBA_Index_Stats](
[DbName] [varchar](128) NULL,
[TableName] [varchar](128) NULL,
[OwnerName] [varchar](128) NULL,
[IndexName] [varchar](128) NULL,
[ClusteredYN] [tinyint] NULL,
[FillFactor] [tinyint] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ScanDensity] [numeric](5, 0) NULL,
[Rows] [int] NULL,
[Pages] [int] NULL,
[Reindex_Factor] [int] NOT NULL CONSTRAINT [DF__DBA_Index___Reind__6E01572D]  DEFAULT ((0)),
[Process_Flag] [tinyint] NULL,
[FillFactor_upd_flag] [tinyint] NULL,
[Process_Date] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DBA_Index_History]    Script Date: 01/29/2008 10:36:04 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBA_Index_History](
[DbName] [varchar](128) NULL,
[TableName] [varchar](128) NULL,
[OwnerName] [varchar](128) NULL,
[IndexName] [varchar](128) NULL,
[ClusteredYN] [tinyint] NULL,
[FillFactor] [tinyint] NULL,
[LogicalFrag] [decimal](18, 0) NULL,
[ScanDensity] [numeric](5, 0) NULL,
[Rows] [int] NULL,
[Pages] [int] NULL,
[Reindex_Factor] [int] NULL,
[Process_Flag] [tinyint] NULL,
[FillFactor_upd_flag] [tinyint] NULL,
[Process_Date] [datetime] NULL,
[Run_Date] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [DBA]
GO

USE [DBA]
GO

/****** Object:  Table [dbo].[DBA_LOB_Indexes]    Script Date: 12/5/2013 7:26:01 AM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DBA_LOB_Indexes](
[DBName] [nvarchar](130) NULL,
[FileGroupName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NULL,
[IndexName] [sysname] NULL,
[LOBUsedPages] [bigint] NOT NULL,
[LOBTotalPages] [bigint] NOT NULL,
[LOBDataSizeMB] [bigint] NULL,
[AllocUnitType] [nvarchar](60) NULL
) ON [PRIMARY]

GO





USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[DBA_dbReindex]    Script Date: 5/12/2014 1:35:24 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[DBA_dbReindex]
@dbNamevarchar(128),
@limitnumeric,
@limit_type             varchar(10),
@ScanDensity_Thresholdint,
@FillFactor_Age_Limitint = 0,
@Test_Run_Flagint,
@offlinebit = 0,
@MinimalLoggingbit = 1,
@MinimumPagesint = 500,
@ExlusionListvarchar(2000) = null

/********************************************************************/                                                                                                                                                      
--                                                           
--  Module Name: DBA_dbReindex                                
--                                                           
--  Description: Receives 6 parms.                          
--@dbName  : database for which reindex is run           
--@limit : max number or percent of indexes to rebuild
--@limit_type : allows for setting @limit as either 'percent' or
--'count'.  if the parameter is populated with anything
--other than percent (or %), count will be used.  please populate
--with one of these two values, to ease understandability.  If
--you wish to reindex the entire database, set @limit = 100 and 
--      @limit_type = 'percent'.           
--@ScanDensity_Threshold: any index with scan density          
--lower than this value will be candidate for rebuild
--@FillFactor_Age_Limit: If an index has been rebuilt within the
--number of days specified by this parameter, the fillfactor will
--be reduced by 5, to a minimum of 70.  If the limit of 70 has been
--reached, a pop-up message will be sent to the on-call dba.  With 
--the default value of 0, no action is taken.   
--@Test_Run_Flag: This flag controls procedure functionality.
--values:
--0: production, no limitations on functionality
--1: test, execution of Alter Index is inhibited. 
--Alter Index command is printed to the screen.
--update statistics is inhibited.
--2: all restrictions included in "1." plus
--DBA.dbo.DBA_dbReindex_showcontig and [dbo].[DBA_LOB_Indexes] is not updated.
--
--                                                            
--    This procedure performs a reindex on a given            
--    database.  It runs DBCC ShowContig to retrieve         
--    information on the indexes so that a decision       
--    can be made which indexes need to be rebuilt and also
--pulls information from sysobjects and sysindexes.          
--    It sorts the indexes from worst to best, and          
--    then rebuilds the '@limit' worst indexes.  
--
--  Written By:  Steve Phelps              
--                                                           
--  Date: December 27, 2002                                    
--                                                            
--  Modified : modified procedure to only explicitly reindex the clustered index if one exists.
--Also changed procedure to use pages rather than rows in determining which indexes
--to rebuild.                                               
--  Date: 6/12/2003                                                    
--                                                            
--  Modified : add LogicalFrag to DBA_Index_Stats.  Set reindex_factor to not null and provide
--          default value of 0.  Don't calc reindex_factor if scandensity < @ScanDensity_Threshold.
--          Add FillFactor_age_limit functionality. Removed FillFactor_Override.
--  Date: 2/05/2004                                                    
--                                                            
--  Modified : added logic to check for existence of table before executing DBCC DBREINDEX.  In
--particular, Goldmine creates and deletes tables dynamically.  At least once, a table was
--deleted after the showcontig, and before the reindex, causing the job to fail.                                              
--  Date:3/4/2004                                                    
--                                                            
--  Modified :  fixed table existence logic.
--  Date: 1/12/2005 sbp                                                    
--
--  Modified : fixed test flag = 1, added "print version:"
--  Date:3/9/2005    
--
--  Modified : changed logic to reindex only those indexes identified by reindex_factor.  Clustered
--indexes are no longer treated differently.  removed update stats.  also added the DBA_index_history
--  table and wrote DBA_dbreindex_history_load, which should be run immediately after this procedure.
--  Eventually, DBA_dbreindex_history_load should be incorporated into this procedure.
--  Date:6/8/2005 sbp   
--  
--Modifications to this code After 6/8/2005 were made by Keith Hays with many thanks to Stepen Phelps
--                                                            
--  USAGE:                                                    
--                                                            
--    -- Before implementing this procedure, be sure that     
--    -- you understand its usage, and are able to make      
--    -- an educated decision as to the values of the         
--    -- parameters, and the scheduling of the task.  This   
--    -- procedure can have a tremendous impact on the       
--    -- performance of a database, either positive or negative.  
--    --
--    --    You can determine the current fill factor for a particular 
--    --    index by running the following query:
--    --    USE pubs
--    --    SELECT INDEXPROPERTY(OBJECT_ID('authors'),    <=  table name
--    --'UPKCL_auidind',         <=  index name
--    --   'IndexFillFactor')       <=  parameter specifying fill factor  
--
--Before implementing this procedure, you should verify that the required tables
--have been created in the DBA database.  This can be done by executing the script
--DBA_dbReindex_create_tables.sql .  This procedure should also be placed in and
--executed from the DBA database.  IF the original script is used to create this
--procedure, this is all taken care of within the script!
--                                                                                                       
-- DECLARE
-- @dbNamevarchar(128),
-- @limitnumeric,
-- @limit_typevarchar(10),
-- @ScanDensity_Thresholdint,
-- @FillFactor_Age_Limitint,
-- @Test_Run_Flagint,
--
-- 
-- select @dbName = 'PUBS'                                
-- select @limit =  50    
-- select @limit_type = 'percent' -- or '%' or 'count'                             
-- select @ScanDensity_Threshold = 80
-- select @FillFactor_Age_Limit = 3                         
-- select @Test_Run_Flag = 0 -- 0 or 1 or 2
-- 
-- exec DBA_dbReindex @dbName, @limit, @limit_type,                 
-- @ScanDensity_Threshold, @FillFactor_Age_Limit, @Test_Run_Flag 
--
-- exec DBA_dbreindex_history_load @dbName, 1, 'month'          
--
/********************************************************************/   


AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY HIGH 
PRINT 'DBA Database Version: 2013/04/13 15:30:00'

-- -- The following Declarations are for test purposes only.
-- 
-- DECLARE
-- @dbName varchar(128),
-- @limit numeric,
-- @limit_type varchar(10),
-- @ScanDensity_Threshold int,
-- @FillFactor_Age_Limit int,
-- @Test_Run_Flag int
-- 
-- select @dbName = 'RSVP_Billing'                                
-- select @limit =  100    
-- select @limit_type = 'percent' -- or '%' or 'count'                             
-- select @ScanDensity_Threshold = 95
-- select @FillFactor_Age_Limit = null                         
-- select @Test_Run_Flag = 1 -- 0 or 1 or 2
-- @offlinebit = 0,
--@MinimalLoggingbit = 1
-- -- end test declarations

DECLARE
@TableNameVARCHAR(128),
@OwnerNameVARCHAR(128),
@IndexNameVARCHAR(128),
@FF_IndexNameVARCHAR(128),
@FillFactorINT,
@CounterINT,
@Inner_CounterINT,
@Inner_LimitINT,
@ClusteredYNINT,
@RowsINT,
@PagesINT,
@ExitTINYINT,
@commandNVARCHAR(2000),
@command2  NVARCHAR(2000),
@parmlist NVARCHAR(100),
@flag TINYINT,
@job_statusTINYINT,
@Todays_DateDATETIME,
@ScanDensity tinyint,
@Reindex_Factor int,
@txt varchar(500),
@string nvarchar(500),
@@MESSAGE varchar(2000),
@@MESSAGE2 VARCHAR(2000),
@@MESSAGE3 VARCHAR(2000),
@SCANMODE NVARCHAR(20),
@LOB BIT,
@RecoveryModelStart nvarchar(60),
@RecoveryModelFinish nvarchar(60),
@c_pos int,
@n_pos int,
@l_pos int

DECLARE @ExlusionsTable Table (DBName varchar(128))



SET @SCANMODE = 'LIMITED' 
SET @dbName = '['+ REPLACE(REPLACE(@DBNAME,'[',''),']','') +']'


IF (select count(*) FROM sys.databases where '['+name+']' = @dbName AND source_database_id IS NOT NULL) > 0
BEGIN
GOTO job_abend
END -- EXIT IF DATABASE IS SNAPSHOT

IF  NOT (@ExlusionList is null OR LTRIM(RTRIM(@ExlusionList)) = '')
BEGIN
IF CHARINDEX(',',@ExlusionList) = 0 or CHARINDEX(',',@ExlusionList) is null
BEGIN
insert into @ExlusionsTable (DBName) values ('['+ REPLACE(REPLACE(REPLACE(UPPER(@ExlusionList),' ',''),'[',''),']','') +']')
END
ELSE
BEGIN
set @c_pos = 0;
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos)

while @n_pos > 0
BEGIN
insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@c_pos+1,@n_pos - @c_pos-1))
set @c_pos = @n_pos
set @l_pos = @n_pos
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos+1)
END

insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@l_pos+1,4000))

UPDATE @ExlusionsTable
SET DBName = '['+ REPLACE(REPLACE(REPLACE(UPPER(DBName),' ', ''),'[',''),']','') +']'
END
IF (SELECT COUNT(*) FROM @ExlusionsTable WHERE DBNAME = @dbName) > 0
BEGIN
GOTO job_abend
END
END -- EXIT IF DATABASE IS EXCLUDED



IF @MinimalLogging = 1 and @Test_Run_Flag = 0
BEGIN
select @RecoveryModelStart = recovery_model_desc
FROM sys.databases where '['+name+']' = @dbName

if @RecoveryModelStart = 'FULL' and @dbName not in ('[Master]', '[Model]', '[MSDB]', '[TEMPDB]', '[SSISDB]')  
begin
SET @command = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY BULK_LOGGED'
EXEC (@command)
SET @command = null
end
IF @RecoveryModelStart = 'BULK_LOGGED'
BEGIN
SET @RecoveryModelStart = 'FULL'
END
END

-- create a temporary table to hold data for indexes for which the fillfactor should be re-calculated.
CREATE TABLE #FillFactor_Table
(
#TblName varchar(128),
#OwnerName varchar(128),
#IndexName varchar(128),
#FillFactor int,
#Process_Flag tinyint,
#Process_Date datetime,
#FF_Flag tinyint default 0
)

-- create a temporary table to hold the results of DBCC Showcontig
CREATE TABLE #Showcontig
(
[#ObjectName] [char] (128),
[#ObjectId] [int],
[#IndexName] [char] (128),
[#IndexId] [int],
[#Lvl] [int],
[#CountPages] [int],
[#CountRows] [int],
[#MinRecSize] [int],
[#MaxRecSize] [int],
[#AvgRecSize] [int],
[#ForRecCount] [int],
[#Extents] [int] NULL,
[#ExtentSwitches] [int],
[#AvgFreeBytes] [int],
[#AvgPageDensity] [int],
[#ScanDensity] [decimal](18, 0),
[#BestCount] [int],
[#ActualCount] [int],
[#LogicalFrag] [decimal](18, 0),
[#ExtentFrag] [decimal](18, 0)
)

CREATE TABLE #indstats 
(
    indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    database_id BIGINT,
    index_id BIGINT,
    Object_IDBigInt
)


if @Test_Run_Flag <> 2
BEGIN
select @command = 
'use ' +  @dbName + '; '+
'SELECT database_id,index_id,object_id
FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,''LIMITED'') ps'


INSERT INTO #indstats
EXEC (@command)
set @command = null

Delete 
from [DBA].dbo.[DBA_LOB_Indexes]
where dbName = @dbName

select @command = 
'use ' +  @dbName + '; '+
'SELECT DISTINCT '''+  @dbname + ''' as DBName, FileGroupName = FILEGROUP_NAME(a.data_space_id)
,TableName = OBJECT_NAME(p.OBJECT_ID)
,IndexName = i.name
,LOBUsedPages = a.used_pages
,LOBTotalPages = a.total_pages
,LOBDataSizeMB = a.used_pages * 8/1024
,a.type_desc AS AllocUnitType


FROM sys.allocation_units a
INNER Join sys.partitions p
ON p.partition_id = a.container_id
And a.type = 2                                  --LOB data is stored in pages of type Text/Image
LEFT Outer Join sys.dm_db_index_usage_stats us
ON us.OBJECT_ID = p.OBJECT_ID
And us.index_id = p.index_id
And us.database_id = DB_ID()
LEFT Outer Join #indstats ps
ON us.index_id = ps.index_id
And us.database_id = ps.database_id
And us.OBJECT_ID = ps.OBJECT_ID
LEFT Outer Join sys.indexes i
ON i.OBJECT_ID = p.OBJECT_ID
And i.index_id = p.index_id
WHERE OBJECTPROPERTY(p.OBJECT_ID,''IsMSShipped'') = 0 and i.name is not null
  ORDER BY dbname, TableName ASC,a.type_desc'



  INSERT INTO [DBA].[dbo].DBA_LOB_INDEXES
   EXEC (@command)
  set @command = null
END  -- if @Test_Run_Flag <> 2 for LOB Information




set @Todays_Date = getdate()

-- Populate Fill Factor information for current db from history.


insert #FillFactor_Table
SELECT     TableName, OwnerName, IndexName, [FillFactor], Process_Flag, MAX(Process_Date) AS Process_Date, 0
FROM         DBA_Index_History
WHERE     dbName = @dbName and (Process_Flag in (1,2))
GROUP BY TableName, OwnerName, IndexName, [FillFactor], Process_Flag




--REBUILD DBA.dbo.DBA_dbReindex_showcontig from sys.dm_db_index_physical_stats
if @Test_Run_Flag <> 2
begin
--  delete records from DBA_dbReindex_showcontig for current db.
Delete 
from DBA.dbo.DBA_dbReindex_showcontig
where dbName = @dbName

-- Build the command to execute a DBCC SHOWCONTIG on the specified database.  The
-- results will be stored in the DBA_dbReindex_showcontig table.
select @command = 
'USE ' + @dbName + '
SELECT OBJECT_NAME(p.object_id) AS ObjectName, p.object_id, isnull(i.name,'''') as IndexName, p.index_id as IndexID, 
p.index_level as Level,
p.page_count as Pages, p.record_count AS [Rows], p.min_record_size_in_bytes AS MiniumumRecordSize, p.max_record_size_in_bytes as MaximumRecordSize,
p.avg_record_size_in_bytes as AverageRecordSize, p.forwarded_record_count as ForwardedRecords,
null as Extents, null as ExtentSwitches, null as AverageFreeBytes, 
p.avg_page_space_used_in_percent as AveragePageDensity, (100-p.avg_fragmentation_in_percent) as ScanDensity,
1 as BestCount, 1 as ActualCount, p.avg_fragmentation_in_percent as LogicalFragmentation,
p.avg_fragmentation_in_percent AS ExtentFragmentation 
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, '+ CHAR(39)+ @SCANMODE + CHAR(39)+') AS p
INNER JOIN sys.sysdatabases AS d ON p.database_id = d.dbid
LEFT OUTER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id'

--print @command
INSERT INTO #showcontig
 EXEC (@command)
set @command = null

INSERT INTO DBA.dbo.DBA_dbReindex_showcontig
select
@dbName,
#ObjectName,
#ObjectId,
#IndexName,
#IndexId,
#Lvl,
#CountPages,
#CountRows,
#MinRecSize,
#MaxRecSize,
#AvgRecSize,
#ForRecCount,
#Extents,
#ExtentSwitches,
#AvgFreeBytes,
#AvgPageDensity,
#ScanDensity,
#BestCount,
#ActualCount,
#LogicalFrag,
#ExtentFrag
from #showcontig
end -- if @Test_Run_Flag <> 2 REBUILD DBA.dbo.DBA_dbReindex_showcontig from sys.dm_db_index_physical_stats


if 1 = 1 --WHICH INDEXES TO REBUILD
BEGIN
--  Build the command to populate the DBA_Index_Stats table.  This is the table
--  that is used to determine which indexes should be rebuilt, and in what order.
--Note:  
--indid = 0 = Heap
--indid = 1 = Clustered index 
--indid = 2 - 249 = nonclustered index 
--indid = 255 is NOT an index, it is an entry for tables specifying that the table has text
--or image column(s).
--This procedure will ignore indid = 0 or indid = 255!
--Delete Index Statistics from Permanant Table before rebuilding
Delete 
from DBA.dbo.DBA_Index_Stats
where dbName = @dbName

select @command = 'use ' +  @dbName + '; '+ 
'SELECT ' + char(39) + @dbName + char(39) + ',
SO.[name],
--SU.[name],
OBJECT_SCHEMA_NAME(SO.ID), 
SI.[name], 
CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END,
CASE WHEN MAX(SI.OrigFillFactor) = 0 THEN 100 ELSE MAX(SI.OrigFillFactor) END,
cast(avg(SC.[LogicalFrag]) as decimal(18,0)) as [LogicalFrag], 
cast(avg(SC.[ScanDensity]) as decimal(18,0)) as [ScanDensity],
SI.[RowCnt],
SUM(SC.[CountPages]) as [CountPages],
0,
0,
Null,
Null
FROM ' + @dbName + '.sys.sysindexes SI
LEFT JOIN ' + @dbName + '.sys.sysobjects SO ON SI.[id] = SO.[id]
LEFT JOIN DBA.dbo.DBA_dbreindex_showcontig SC ON SI.[id] = SC.[ObjectId]
AND SI.[IndId] = SC.[IndexId]
AND SC.dbName = ' + char(39) + @dbName + char(39) + '
LEFT JOIN ' + @dbName + '.sys.sysusers SU ON SO.uid = SU.uid
WHERE SO.[xtype] = ''U'' 
--AND SO.[status] > 0
AND SI.indid <> 0 
AND SI.indid <> 255
AND SC.[ScanDensity] < ' + CAST(@ScanDensity_Threshold AS VARCHAR(25)) + '
AND SI.[dpages] > 8 ' + ' 
         AND ' + char(39) + @dbName + char(39) + ' IN  (select + CHAR(91) + NAME + CHAR(93) from sys.databases where is_read_only = 0 and name not in (''tempdb''))
GROUP BY SO.[name], OBJECT_SCHEMA_NAME(SO.ID), SI.[name], SI.indid, SI.[RowCnt]
HAVING SUM(SC.[CountPages]) > ' + cast(@MinimumPages as varchar(25)) + ''
--CHECK FOR READ_ONLY ADDED 12/4/2007\
--CHECK FOR SCHEMA OWNER DIFFRERENT FROM SCHEMA 4-10-2013
--print @command
--goto job_abend
insert DBA.dbo.DBA_Index_Stats
 EXEC (@command)
set @command = null

END -- WHICH INDEXES TO REBUILD

  
if @FillFactor_Age_Limit > 0
-- If the fillfactor age limit parameter is populated, calculate a new fill factor for indexes which have been
-- rebuilt within that time limit, and are candidates for rebuilding.  
-- Note that we are looking at the DBA_Index_History table for previous run information.
BEGIN
update #FillFactor_Table
set #FillFactor = 
case
when I.[FillFactor]= 100 and I.ScanDensity < @ScanDensity_Threshold then 95
when I.[FillFactor] between 50 and 55 and I.ScanDensity < @ScanDensity_Threshold then 50
when I.[FillFactor] between 55 and 99 and I.ScanDensity < @ScanDensity_Threshold then I.[FillFactor] - 5
when I.[FillFactor] = 0 then 100
else I.[FillFactor] 
end 
from DBA.dbo.DBA_Index_History I
join #FillFactor_Table F
on  I.dbName = @dbName
and I.TableName = F.#tblname
and I.OwnerName = F.#OwnerName
and I.IndexName = F.#indexname
and I.Process_Date = F.#Process_Date
and F.#Process_Flag = 1
andDateDiff(DD, Process_Date, @Todays_Date) <= @FillFactor_Age_Limit
END  --CHECK FILL FACTOR AGE LIMIT


-- The following code calculates a "priority" for reindexing based on Scan Density and the 
-- number of pages in the table.  Tables with more pages receive a higher priority, and 
-- indexes with a lower Scan Density receive a higher priority.  An index with a high Scan
-- density may receive a higher priority than one with an extremely low Scan Density if 
-- it has a much greater number of pages.  This is desirable, as a low Scan Density has 
-- little meaning in a table with few pages.  Also, a Reindex_Factor will not be calculated
-- if the ScanDensity = 100 or is greater than the threshold.
update DBA.dbo.DBA_Index_Stats
set Reindex_Factor = (((100 - ScanDensity) / 100) * [Pages])
from DBA.dbo.DBA_Index_Stats
where ScanDensity < @ScanDensity_Threshold
and dbName = @dbName

-- Get the number of indexes that are candidates for rebuilding.
select @Rows = count(*)
from DBA.dbo.DBA_Index_Stats
where ScanDensity <= @ScanDensity_Threshold
and dbName = @dbName

-- If @limit_type is percent, calculate @limit percent of @Rows.  If @limit_type is any
-- value other than '%' or 'percent', 'count' is assumed, and @limit is used as a literal.
if @limit_type = '%' or 
@limit_type = 'percent'
begin 
select @limit = (@limit * .01) * @Rows
end

-- It is possible for @limit to be greater than the number of indexes that are candidates.  
-- If this occurs, reset @limit to be equal to @Rows.
if @limit > @Rows
set @limit = @Rows

set @Exit = 0
set @Counter = 1
While @Counter <= @limit and @Exit = 0 -- Index Build While @Counter
Begin
-- Retrieve data from DBA_Index_Stats to build the ALTER INDEX command.  The table is read in
-- Reindex_Factor descending order.  Once a candidate has been selected, it is reindexed with
-- the ALTER INDEX command, then Process_Flag is set to '1', eliminating it from the pool.  

-- Initiate processing for the current index.
--Select top 1
--@TableName = TableName,
--@OwnerName = OwnerName,
--@IndexName = IndexName,
--@FillFactor = [FillFactor]
--from DBA.dbo.DBA_Index_Stats
--where Process_Flag = 0
--and dbName = @dbName
--Order by
--Reindex_Factor desc,
--ScanDensity Asc,
--Pages Desc,
--LogicalFrag desc
SELECT   TOP 1 
@TableName = TableName, 
@OwnerName = OwnerName, 
@IndexName = IndexName, 
@FillFactor = [FillFactor]
FROM    DBA.dbo.DBA_Index_Stats
where Process_Flag = 0 and dbName = @dbName
GROUP BY TableName, OwnerName, IndexName, [FillFactor]
ORDER BY SUM(Reindex_Factor) DESC

-- Check if a record exists in #FillFactor_table.  If so, set the FillFactor for the 
-- DBCC DBReindex command = the value from #FillFactor_table.
If exists(select 1 from #FillFactor_Table where #Tblname = @TableName and #IndexName = @IndexName)
begin
select @FillFactor = #FillFactor
from #FillFactor_Table
where #Tblname = @TableName 
and #IndexName = @IndexName
update #FillFactor_Table
set #FF_Flag = 1
where #Tblname = @TableName
and #IndexName = @IndexName
end
--CHECK IF INDEX CONTAINS LOB
SET @LOB = 0
If exists(select 1 from DBA.[dbo].[DBA_LOB_Indexes] where [TableName] = @TableName and IndexName = @IndexName AND [DbName] = @dbName)
BEGIN
SET @LOB = 1
END
Process_Index:

-- Build the Alter Index command


IF LEFT(CAST(SERVERPROPERTY ('edition') AS VARCHAR(1)),1) = 'E' and @LOB = 0 and @offline = 0
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']'
+ ' REBUILD WITH ' 
+ '(' + 'FILLFACTOR=' + RTRIM(@FillFactor) + CHAR(44) + SPACE(1)
+ 'ONLINE=ON' + CHAR(44) + SPACE(1)
+ 'MAXDOP=2' + ')'
END
ELSE IF LEFT(CAST(SERVERPROPERTY ('edition') AS VARCHAR(1)),1) = 'E' and @LOB = 1 and @offline = 1
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']'
+ ' REBUILD WITH ' 
+ '(' + 'FILLFACTOR=' + RTRIM(@FillFactor) + CHAR(44) + SPACE(1)
+ 'ONLINE=OFF' + CHAR(44) + SPACE(1)
+ 'MAXDOP=2' + ')'
END
ELSE IF LEFT(CAST(SERVERPROPERTY ('edition') AS VARCHAR(1)),1) = 'E' and @LOB = 0 and @offline = 1
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']'
+ ' REBUILD WITH ' 
+ '(' + 'FILLFACTOR=' + RTRIM(@FillFactor) + CHAR(44) + SPACE(1)
+ 'ONLINE=OFF' + CHAR(44) + SPACE(1)
+ 'MAXDOP=2' + ')'
END
ELSE
BEGIN
Set @command = 
'Alter Index ' + @IndexName 
+ ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']' 
+ ' REORGANIZE  ' 

END
print @command

set @string = @dbName + '.' + @OwnerName + '.' + @TableName
-- Verify that the table still exists before executing the reindex.  Goldmine, in particular, has a nasty habit
-- of deleting tables when it sees fit.  This has caused this procedure to generate errors, leading to this solution.
set @command2 = N'select @flag = 1 from ' + @DBName + N'.dbo.sysobjects where id = object_id(''' + @string + N''') and xtype = ''U'''
set @parmlist = '@flag as tinyint OUTPUT'
EXEC sp_executesql @command2, @parmlist, @flag OUTPUT
if @Test_Run_Flag = 0 
BEGIN
if @flag = 1
BEGIN
BEGIN TRY
SET @@MESSAGE3 = @command
 exec sp_executesql @command
 set @@MESSAGE = 'REINDEXING SUCCEEDED: ' + @IndexName + ' on ' + @dbName + '.[' + @OwnerName + '].[' + @TableName + ']' + ' FILLFACTOR=' + RTRIM(@FillFactor) 
 EXEC master..xp_logevent 60000, @@MESSAGE, informational
 END TRY
 BEGIN CATCH
 SET @@MESSAGE2 = 'REINDEXING FAILED FOR INDEX COMMAND: ' + @@MESSAGE3 + ' ERROR: ' + ERROR_MESSAGE()
EXEC master..xp_logevent 60000, @@MESSAGE2, informational
RAISERROR(@@MESSAGE, 16, 1)
END CATCH
END -- if @flag = 1
END -- if @Test_Run_Flag = 0
--write to logs


-- Set Process_Flag in DBA_Index_Stats for the index we just rebuilt to 1, eliminating it from the potential
-- indexes to rebuild.  
update DBA.dbo.DBA_Index_Stats
set 
Process_Flag = 1,
Process_Date = @Todays_Date
where dbName = @dbName
and TableName = @TableName
andIndexName = @IndexName

-- increment counter
set @Counter = @Counter + 1

-- if all indexes that are candidates have been rebuilt, get out of the loop.
if not exists (select 1 from DBA.dbo.DBA_Index_Stats 
where dbName = @dbName and Process_Flag = 0 and ScanDensity < @ScanDensity_Threshold)
set @Exit = 1 
end -- Index Build While @Counter

select @RecoveryModelFinish = recovery_model_desc
FROM sys.databases where '['+name+']' = @dbName

if @RecoveryModelFinish <> @RecoveryModelStart and @dbName not in ('[Master]', '[Model]', '[MSDB]', '[TEMPDB]', '[SSISDB]')
begin
SET @command = 'ALTER DATABASE ' + @DBNAME + ' SET RECOVERY '+ @RecoveryModelStart
EXEC (@command)
SET @command = null
end

-- Fill Factor notification here...
--/*

-- update DBA_Index_Stats with new fillfactor, and set FillFactor_upd_flag to show that the fillfactor was updated.
update DBA.dbo.DBA_Index_Stats
set FillFactor_upd_flag = 1,
[FillFactor] = F.#FillFactor
from DBA.dbo.DBA_Index_Stats I
join #FillFactor_Table F
on I.TableName = F.#tblname
and I.IndexName = F.#IndexName
and I.[FillFactor] <> F.#FillFactor
and I.dbName = @dbName
and I.Process_Flag = 1


-- send an alert to the on call dba when any fillfactor reaches 70.  this alert should only be sent when
-- the fillfactor CHANGES to 70, not if it was 70 before running DBA_dbReindex.
-- Change the following code to check against DBA_index_history, checking the previous run for fillfactor > 70.  This will 
-- ensure that the alert will only be fired when the fillfactor actually CHANGES to 70.
-- if exists(select 1 from DBA.dbo.DBA_Index_Stats where [FillFactor] = 70 and FillFactor_upd_flag = 1 and dbName = @dbName)
-- exec a_AlertCall 15

cleanup:

drop table #FillFactor_Table
drop table #showcontig
drop table #indstats
-- test stuff 
/*
select * from #fillfactor_table
select * from DBA_Index_Stats order by dbName, Reindex_Factor desc
select * from DBA_dbReindex_showcontig
truncate table DBA_index_stats

select count(*) from DBA.dbo.DBA_Index_Stats

select count(*) from #fillfactor_table
*/-- end test stuff

job_abend:
if @@error <> 0
return @@error















GO













USE [DBA]
GO

/****** Object:  StoredProcedure [dbo].[DBA_dbreindex_history_load]    Script Date: 1/22/2014 4:42:34 PM ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE  [dbo].[DBA_dbreindex_history_load]

(@dbNamesysname = NULL, 
@retentionint = 30,
@retention_unit varchar(10) = 'day',
@ExlusionListvarchar(2000) = null
)

AS                        
SET NOCOUNT ON

/*************************************************************/                                                                                                                                                      
--                                                   
-- Module Name: DBA_dbreindex_history_load   
--                                                
-- Description: 
--   3 input parm(s). 
-- @dbName sysname : Name of the database that reindex data will be archived for.                 
--     @retention int : history will be held for this number of retention units.
--     @retention_unit varchar(10) : units for @retention (for the DATEADD function).
--Valid @retention_units are day, week, month, quarter, year.
--
-- Procedure description.
--This procedure migrates DBA_index_stats data to the DBA_index_history table, and deletes
-- the old history from the table.  Retention defaults to one month.
                                                  
-- Written By: Steve Phelps            
--                                                  
-- Date: June 02, 2005         
--                                                   
-- Modified :                                       
-- Date: 
-- Change:                                           
--                                                  
-- USAGE:                                            
--                                                  
--   exec DBA_dbreindex_history_load @databasename, @retention, @retention_unit     
--                            
/*************************************************************/   

PRINT 'Version: 2014/01/22 14:30:00
' 
SET @dbName = '['+ REPLACE(REPLACE(@DBNAME,'[',''),']','') +']'


-- the following declare must be removed. its here for testing.
-- DECLARE
--@dbName sysname,
-- @retention int,
-- @retention_unit varchar(10),  -- day, week, month, quarter, year
-- SELECT 
--@dbName = 'RSVP_Production',
--@retention = 1, 
--@retention_unit = 'month'
-- end of test logic




IF @dbName is NULL
BEGIN
RAISERROR ('Database name must be entered.', 16, 1)
RETURN -1
END

IF @retention_unit NOT IN ('day', 'week', 'month', 'quarter', 'year')
BEGIN
RAISERROR ('Valid @retention_units are day, week, month, quarter, year.', 16, 1)
RETURN -1
END

DECLARE @ExlusionsTable Table (DBName varchar(128))
DECLARE @c_pos int
DECLARE @n_pos int
DECLARE @l_pos int

IF  NOT (@ExlusionList is null OR LTRIM(RTRIM(@ExlusionList)) = '')
BEGIN
IF CHARINDEX(',',@ExlusionList) = 0 or CHARINDEX(',',@ExlusionList) is null
BEGIN
insert into @ExlusionsTable (DBName) values ('['+ REPLACE(REPLACE(REPLACE(UPPER(@ExlusionList),' ',''),'[',''),']','') +']')
END
ELSE
BEGIN
set @c_pos = 0;
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos)

while @n_pos > 0
BEGIN
insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@c_pos+1,@n_pos - @c_pos-1))
set @c_pos = @n_pos
set @l_pos = @n_pos
set @n_pos = CHARINDEX(',',@ExlusionList,@c_pos+1)
END

insert into @ExlusionsTable (DBName) values (SUBSTRING(@ExlusionList,@l_pos+1,4000))

UPDATE @ExlusionsTable
SET DBName = '['+ REPLACE(REPLACE(REPLACE(UPPER(DBName),' ', ''),'[',''),']','') +']'
END
IF (SELECT COUNT(*) FROM @ExlusionsTable WHERE DBNAME = @dbName) > 0
BEGIN
GOTO job_abend
END
END -- EXIT IF DATABASE IS EXCLUDED

DECLARE @MinDate datetime
SELECT @retention = @retention * -1

SELECT @MinDate = 
CASE
WHEN @retention_unit = 'day' THEN DATEADD(day, @retention, getdate())
WHEN @retention_unit = 'week' THEN DATEADD(week, @retention, getdate())
WHEN @retention_unit = 'month' THEN DATEADD(month, @retention, getdate())
WHEN @retention_unit = 'quarter' THEN DATEADD(quarter, @retention, getdate())
WHEN @retention_unit = 'year' THEN DATEADD(year, @retention, getdate())
END 

-- delete historical data which is older than the retention period.
DELETE FROM DBA..DBA_Index_History
WHERE Run_Date < @MinDate
AND DbName = @dbName

-- Load the current run's data into the history table.
INSERT INTO [DBA].[dbo].[DBA_Index_History]
([DbName], [TableName], [OwnerName], [IndexName], 
[ClusteredYN], [FillFactor], [LogicalFrag], [ScanDensity], 
[Rows], [Pages], [Reindex_Factor], [Process_Flag], 
[FillFactor_upd_flag], [Process_Date])
SELECT 
[DbName], [TableName], [OwnerName], [IndexName], 
[ClusteredYN], [FillFactor], [LogicalFrag], [ScanDensity], 
[Rows], [Pages], [Reindex_Factor], [Process_Flag], 
[FillFactor_upd_flag], [Process_Date] 
FROM [DBA].[dbo].[DBA_Index_Stats]
WHERE DbName = @dbName


job_abend:
if @@error <> 0
return @@error
GO



USE [msdb]
GO

/****** Object:  Job [Dynamic Reindex job]    Script Date: 04/13/2013 09:47:53 ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/13/2013 09:47:53 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Dynamic Reindex job', 
@enabled=0, 
@notify_level_eventlog=2, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dynamically Reindex All Databases]    Script Date: 04/13/2013 09:47:53 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dynamically Reindex All Databases', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=3, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC DBA.[dbo].[DBA_dbReindex] ''?'', 100, ''percent'', 90, 3, 0"', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Load Index History]    Script Date: 04/13/2013 09:47:53 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Load Index History', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC [dbo].[DBA_dbreindex_history_load] ''?'', 30, ''day''"', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule 1', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=1, 
@freq_subday_interval=1, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20071101, 
@active_end_date=99991231, 
@active_start_time=10000, 
@active_end_time=235959, 
@schedule_uid=N'09a4ff5a-77ef-4109-a13a-683f11b16321'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

USE [msdb]
GO

/****** Object:  Job [Dynamic_Offline_Reindexing]    Script Date: 2/14/2014 4:27:19 PM ******/BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2/14/2014 4:27:19 PM ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Dynamic_Offline_Reindexing', 
@enabled=0, 
@notify_level_eventlog=2, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'No description available.', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Dynamically Offline Reindex All Databases]    Script Date: 2/14/2014 4:27:19 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Dynamically Offline Reindex All Databases', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=3, 
@on_success_step_id=0, 
@on_fail_action=3, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC DBA.[dbo].[DBA_dbReindex] ''?'', 100, ''percent'', 91, 3, 0, 1, 1, 250, null"

--EXECUTE @RC = [dbo].[DBA_dbReindex] 
--   @dbName = ?
--  ,@limit = 100
--  ,@limit_type = ''percent''
--  ,@ScanDensity_Threshold = 91
--  ,@FillFactor_Age_Limit = 3
--  ,@Test_Run_Flag = 0
--  ,@offline = 1
--  ,@MinimalLogging = 1
--  ,@MinimumPages = 250
--  ,@ExlusionList = null', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Load Index History]    Script Date: 2/14/2014 4:27:19 PM ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Load Index History', 
@step_id=2, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec sys.sp_MSforeachdb @command1="EXEC [dbo].[DBA_dbreindex_history_load] ''?'', 30, ''day''"', 
@database_name=N'DBA', 
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Rate

3 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (3)

You rated this post out of 5. Change rating