April 10, 2011 at 6:12 pm
We usually have a reorganize index task set up as a maintenace plan for weekend for databases.
On one of our servers this week we found the job failed with the error message:
Executing query "...... Executing query "USE [mydwStaging] ".: 50% complete End Progress Progress: 2011-04-03 00:48:57.18 Source: Check Database Integrity Task Executing query "DBCC CHECKDB(N'mydwStaging') WITH NO_INFOMSGS ".: 100% complete End Progress Progress: 2011-04-03 00:49:23.66 Source: Reorganize Index Task Executing query "USE [mydwStaging] ".: 0% complete End Progress Progress: 2011-04-03 00:49:23.79 Source: Reorganize Index Task Executing query "ALTER INDEX [idx_DimAbsenceReason_AbsenceReasonCod...".: 0% complete End Progress Progress: 2011-04-03 00:49:23.80 Source: Reorgan... The package execution fa... The step failed.
This is db is a big one abut 120 gb, and it takes above process 5 hours to end, with a failure. In the log, I can see also an error that tempdb is full.
What could be the reason for this, is that possible for disk space is not enough?
April 10, 2011 at 9:20 pm
Hi,
I would guess that it is very likely, but it's difficult to say since you didn't specify how is space organized (which files are sitting where and how much space is there available).
Hi,
Please check this link, you might find it useful:
http://msdn.microsoft.com/en-us/library/ms191183.aspx
The way how index rebuild affects size of TempDB database depends on the settings (whether you have sort_in_tempdb on/off).
But, the fastest way to calculate and get the idea of how much space you would need is IndexSize*2.5.
I didn't really get it if you are rebuilding all the indexes on a weekly basis?
Maybe it's not bad idea to check statistics for the fragmentation and rebuild only some of them whose performances would be really improved by the process.
(check dynamic management views: sys.dm_db_index_usage_stats, sys.dm_db_index_physical_stats, sys.dm_db_index_operational_Stats
but bear in mind that statistics are reset after server is rebooted)
It could be also automated - procedure that would check fragmentation and rebuild only those that meet certain criteria (sorry don't have proc to provide, but sure that you can find it somewhere on the net)
April 10, 2011 at 10:10 pm
Thank you, it's helpful!
April 11, 2011 at 8:50 am
Just wanted to throw this out there: I don't believe you should never set a maximum for the tempdb files (more specifically the LDF files), it's better to let them grow and keep growing. That being said, if you have a regular maintenance window then plan for an mssql restart periodically or restarting the server - this will flush out everything in the tempdb and will be like starting "fresh"
Adding to the index scripts, I've implemented two scripts here at our company approximately 6 weeks that have worked extremely well - we've eliminted the need to have our regular saturday morning 6hr maintenance window!
The script "dba_RebuildORReorgIndexesByFragLevel" can be executed by itself on any server or can be used in conjunction with "dba_ExecuteServerIndexMaintenance" to be executed against all databases on the server (I currently have it running via an Agent job running against 16 servers).
If you run the scripts it will automatically pick up your DB's and show you the fragmentation level
for each index. The scripts will automatically check to see if the index should be rebuilt or reorganized and can tell if it needs to be rebuilt online or offline (in case you have a lot of BLOB-/x-type datatypes in your DB's).
It runs quite well...you can test it before actually running it by setting the @Exec parameter to zero.
Main Script for single database:
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] Script Date: 04/11/2011 09:39:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_ReBuildOrReorgIndexesByFragLevel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_ReBuildOrReorgIndexesByFragLevel]
GO
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] Script Date: 04/11/2011 09:39:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ############################################################################################################### */
--DYNAMICALLY REORG/REBUILD SERVER INDEXES BY FRAGMENTATION LEVEL
/* ############################################################################################################### */
CREATE PROCEDURE [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] (
@DB varchar(50),
@FillFactor varchar(2),
@MinPages int = 50,
@Exec int,
@FragStart int = 10,
@FragEnd int = 25
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Written to dynamically perform maintenance on indexes if fragmentation gets too high
Department:DBA
Created For:DBA's
----------------------------------------------------------------------------------------------------------------
NOTES:* Primary concept originally created by Microsoft *
* Error corrected by Pinal Dave (http://www.SQLAuthority.com) *
* Omits indexes where total pages < 100
----------------------------------------------------------------------------------------------------------------
Created On:03/14/2011
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
Modified On:03/18/2011
Modified By:MyDoggieJessie
Changes:
1.Changed datatypes from varchar to sysname for TName, @tablename, and @IdxName
----------------------------------------------------------------------------------------------------------------
exec dbo.dba_ReBuildOrReorgIndexesByFragLevel 'CORELIBRARY', '92', 2, 0
*/
SET NOCOUNT ON
IF OBJECT_ID ('tempdb.dbo.#fraglist','u') IS NOT NULL
BEGIN
DROP TABLE #fraglist
END
IF OBJECT_ID ('tempdb.dbo.#tables','u') IS NOT NULL
BEGIN
DROP TABLE #tables
END
-- Declare variables
DECLARE @TableNamesysname,@sqlvarchar(1500)
DECLARE @objectidint,@XTypevarchar(3)
DECLARE @indexidint,@fragdecimal
DECLARE @IdxNamesysname,@Recsint
DECLARE @Errvarchar(500),@iint
DECLARE @Versionvarchar(3),@Schemavarchar(3)
DECLARE @Threshold1decimal,@Threshold2decimal
SET @i = 1
SET @Version = UPPER(CONVERT(varchar(3), SERVERPROPERTY('edition')))
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
CREATE TABLE #Tables (
Idx int IDENTITY(1,1),
TName sysname,
TType varchar(3),
SSchema varchar(3)
)
SET @sql = '
SELECT CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name, ''ON'' AS Table_Type, TABLE_SCHEMA
FROM ' + RTRIM(@DB) + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND [table_name] NOT IN (
SELECT DISTINCT a.name
FROM ' + RTRIM(@DB) + '.sys.sysobjects AS a WITH(READUNCOMMITTED)
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS b WITH(READUNCOMMITTED) ON
a.id=b.id
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS c WITH(READUNCOMMITTED) ON
c.xtype=b.xtype
WHERE b.xType IN (''34'',''35'',''99'',''241''))
AND TABLE_NAME NOT LIKE ''MS%''
UNION
SELECT CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name, ''OFF'' AS Table_Type, TABLE_SCHEMA
FROM ' + RTRIM(@DB) + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND [table_name] IN (
SELECT DISTINCT a.name
FROM ' + RTRIM(@DB) + '.sys.sysobjects AS a WITH(READUNCOMMITTED)
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS b WITH(READUNCOMMITTED) ON
a.id=b.id
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS c WITH(READUNCOMMITTED) ON
c.xtype=b.xtype
WHERE b.xType IN (''34'',''35'',''99'',''241''))
AND TABLE_NAME NOT LIKE ''MS%''
ORDER BY TABLE_NAME;'
INSERT INTO #Tables
EXEC(@SQL)
-- Create the temporary table.
CREATE TABLE #fraglist (
ObjectName sysname,ObjectId int,IndexName sysname,
IndexId int,Lvl int,CountPages int,
CountRows int,MinRecSize int,MaxRecSize int,
AvgRecSize int,ForRecCount int,Extents int,
ExtentSwitches int,AvgFreeBytes int,AvgPageDensity int,
ScanDensity decimal,BestCount int,ActualCount int,
LogicalFrag decimal,ExtentFrag decimal
)
SET @Recs = (SELECT COUNT(1) FROM #Tables)
WHILE @i <= @Recs
BEGIN
SET @TableName = (SELECT TOP 1 TName FROM #Tables WHERE Idx = @i)
SET @Schema = (SELECT TOP 1 SSchema FROM #Tables WHERE Idx = @i)
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @DB + '.' + @Schema + '.[' + @TableName + ']'')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
SET @i = @i + 1
END
/* Add column needed so we can perform ON/OFFLINE statements */
ALTER TABLE #fraglist
ADD idxType varchar(3) NULL
IF RTRIM(@Version) = 'STA'
BEGIN
UPDATE #fraglist
SET idxType = 'OFF' FROM #Tables WHERE ObjectName = TName
END
ELSE
BEGIN
UPDATE #fraglist
SET idxType = TType FROM #Tables WHERE ObjectName = TName
END
/* Remove records not needed for the Cursor */
DELETE FROM #fraglist
WHERE CountPages < @MinPages
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT
ObjectName, ObjectId, IndexId, LogicalFrag, IndexName,
CASE WHEN INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') IS NULL THEN 'OFF'
ELSE idxType END
FROM #fraglist
WHERE
CountPages > @MinPages --LogicalFrag >= @MaxFrag
AND ISNULL(INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth'), 1) > 0
ORDER BY LogicalFrag DESC
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @TableName, @objectid, @indexid, @frag, @IdxName, @XType
SET @i = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Exec = 0)
BEGIN
SET @i = @i + 1
IF @frag BETWEEN @FragStart AND @FragEnd
BEGIN
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
END
IF @frag > @FragEnd
BEGIN
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
END
END
ELSE
BEGIN
SET @i = @i + 1
IF @frag BETWEEN @FragStart AND @FragEnd--> Reorganize indexes
BEGIN
SELECT @sql = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
BEGIN TRY
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ('ERROR FOUND: ' + ERROR_MESSAGE())
END CATCH
END
IF @frag > @FragEnd--> Rebuild Indexes
BEGIN
SELECT @sql = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
BEGIN TRY
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ('ERROR FOUND: ' + ERROR_MESSAGE())
END CATCH
END
END
FETCH NEXT
FROM indexes
INTO @TableName, @objectid, @indexid, @frag, @IdxName, @XType
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
/*
exec dbo.dba_ReBuildOrReorgIndexesByFragLevel 'CORELIBRARY', '96',2, 0
*/
-- Delete the temporary table.
DROP TABLE #fraglist
DROP TABLE #Tables
GO
Script to execute the above script across all non-system databases:
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ExecuteServerIndexMaintenance] Script Date: 04/11/2011 09:39:40 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_ExecuteServerIndexMaintenance]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_ExecuteServerIndexMaintenance]
GO
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[dba_ExecuteServerIndexMaintenance] Script Date: 04/11/2011 09:39:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ############################################################################################################### */
--EXECUTE SERVER INDEX MAINTENANCE
/* ############################################################################################################### */
CREATE PROCEDURE [dbo].[dba_ExecuteServerIndexMaintenance](
@FillFactor varchar(2),
@MinPages varchar(2) = '50',
@Exec varchar(1)
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Executes F1Settings.dbo.dba_ReBuildOrReorgIndexesByFragLevel to run against ALL DB's on the
EXECUTING SERVER; excluding master, tempdb, model, msdb, and distribution
Department:DBA
Created For:DBA's
----------------------------------------------------------------------------------------------------------------
NOTES:1)* * * * THIS SHOULD NEVER BE RUN AGAINST PRODUCTION!!! PRODUCTION runs the procedure
dba_ReBuildOrReorgIndexesByFragLevel individually for the CC databases * * * *
2)Passes in parameters to the primary stored-procedure
----------------------------------------------------------------------------------------------------------------
Created On:03/15/2011
Create By:MyDoggieJessie----------------------------------------------------------------------------------------------------------------
Modified On:
Modified By:
Changes:
1.
----------------------------------------------------------------------------------------------------------------
exec dbo.dba_ExecuteServerIndexMaintenance '92', 2, 0
*/
SET NOCOUNT ON
DECLARE @iint
DECLARE @Recsint
DECLARE @Tablesysname
DECLARE @sqlnvarchar(500)
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
/* Create Temp Table to store the results in */
CREATE TABLE #Results (
Idx int IDENTITY(1,1), TName sysname
)
/* Fetch All the DB's on the Server */
INSERT INTO #Results
EXEC sp_MSForEachDB 'Use [?]; SELECT DB_NAME()'
/* Get rid of the ones we don't want to index */
DELETE FROM #Results
WHERE TName IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB', 'DISTRIBUTION', 'AdventureWorks')
/* Loop through the DB's and kick off the magic */
SET @recs = (SELECT COUNT(1) FROM #Results)
WHILE @Recs <> 0
BEGIN
SET @TABLE = (SELECT Top 1 TName FROM #Results )
SET @sql = ' exec F1Settings.dbo.dba_ReBuildOrReorgIndexesByFragLevel '''
+ @Table + ''', ''' + @FillFactor + ''', ' + @MinPages + ', ' + @Exec
EXEC sp_executesql @sql
DELETE FROM #Results WHERE TName = @Table
SET @recs = (SELECT COUNT(1) FROM #Results)
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
DROP TABLE #Results
/*
exec dbo.dba_ExecuteServerIndexMaintenance '92', 2, 0
*/
SET NOCOUNT OFF
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2011 at 9:23 am
I agree with the last idea.....I do the same thing ....helps a lot!
dont need to rebuild everything.....you will more then likely have some tables that are small and re indexing wont help... no matter what....so you can set something in the where clause if its not already there that says if less then table/index is < 100 pages dont rebuild
April 11, 2011 at 10:04 am
In the scripts above, there's just that:
@MinPages int = 50, (could be any # of course)
@FragStart int = 10,
@FragEnd int = 25
/* Remove records not needed for the Cursor */
DELETE FROM #fraglist
WHERE CountPages < @MinPages
/* Further down the code ... */
IF @frag BETWEEN @FragStart AND @FragEnd
--> Performs a REORG
IF > @FragEnd
--> Performs a REBUILD
Otherwise it leaves everything alon, skipping indexes that don't need maintenance 😉
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2011 at 10:10 am
Seriously, stop wasting time on this script. It's been done, extremely well done already.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply