reorg index failed and tempb is full

  • 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?

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

  • Thank you, it's helpful!

  • 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

  • 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

  • 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

  • Seriously, stop wasting time on this script. It's been done, extremely well done already.

    http://sqlfool.com/2010/04/index-defrag-script-v4-0/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply