none
auto index script problem.

    Question

  • USE [msdb]
    GO

    /****** Object:  StoredProcedure [dbo].[SQLAutoIndexPrepDataCapture]    Script Date: 02/16/2014 23:19:58 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO

    CREATE PROC [dbo].[SQLAutoIndexPrepDataCapture]
     @ScanMode VARCHAR(15) = 'LIMITED' -- LIMITED, SAMPLED, or DETAILED; from fast / non-leaf scan to slow / full B-Tree
    AS

    SET NOCOUNT ON

    --DECLARE @db_id SMALLINT
    DECLARE @db_id BIGINT
    DECLARE @SQLString NVARCHAR(2500)
    DECLARE @InternalDBName NVARCHAR(128)

    IF (Object_Id('tempdb..#tbl_InstancePrepDatabases')) Is Not Null Drop Table #tbl_InstancePrepDatabases
    --the following table is used to hold the data relating to the Db state, this is also used to control the looping of databases.
    CREATE TABLE #tbl_InstancePrepDatabases
    (int_InstDb_RowRef bigint  IDENTITY(1,1) PRIMARY KEY, --Constraint name not given as this can cause conflicts when used with local temp tables
    --int_DbId    smallint,
    int_DbId    bigint,
    nvchr_DbName  nvarchar(125))

    CREATE INDEX [Ix_Table_#tbl_InstancePrepDatabases_Col_int_DbId]
      ON [dbo].[#tbl_InstancePrepDatabases](int_DbId) INCLUDE (nvchr_DbName)

    INSERT INTO #tbl_InstancePrepDatabases
    (nvchr_DbName, int_DbId)
    SELECT [name], [database_id]
    FROM sys.databases
    WHERE ([state] + is_in_standby + is_read_only) = 0 AND
     [name] NOT IN (SELECT DISTINCT [nvchr_DbName]
        FROM systems..SQLAutoIndex_Exclusions
          UNION
          SELECT 'master'
          UNION
          SELECT 'model'
          UNION
          SELECT 'msdb'
          UNION
          SELECT 'Mssqlresourcedb' --this should not even show up, but for completeness added it to system Db exculsions
          UNION
          SELECT 'tempdb'
          UNION
          SELECT 'systems')

    --DECLARE @intInstDbCounter smallint
    DECLARE @intInstDbCounter bigint

    SET @intInstDbCounter = (SELECT ISNULL(MAX(int_InstDb_RowRef),0) FROM #tbl_InstancePrepDatabases)

    IF @intInstDbCounter = 0 RETURN --This means that there are no databases in this instances which are in a state which allows them to be defraged

    --the databases contained within the temp table are looped through in reverese order
    WHILE @intInstDbCounter > 0
    BEGIN
     --sets the two variables used throughout the loop to control the database being worked on
     SET @db_id = (SELECT int_DbId FROM #tbl_InstancePrepDatabases WHERE int_InstDb_RowRef = @intInstDbCounter)
     SET @InternalDBName = (SELECT nvchr_DbName FROM #tbl_InstancePrepDatabases WHERE int_InstDb_RowRef = @intInstDbCounter)

     --deletes any existing data held for the database about to be analysed
     SET @SQLString = 'DELETE FROM systems.dbo.tbl_INDEX_FragData_Detailed WHERE intDbId = ' + CAST(@db_id as varchar)
     EXEC (@SQLString)

     --updateds the summary table to signify that the data for the relevant database has been purged
     SET @SQLString = 'UPDATE systems.dbo.tbl_INDEX_FragData_DB_Summary SET dte_DataPurged = GETUTCDATE() WHERE dte_DataPurged is null AND '
     SET @SQLString =  @SQLString + 'dte_DataRfshStart IN (SELECT MAX(dte_DataRfshStart) FROM systems.dbo.tbl_INDEX_FragData_DB_Summary WHERE intDbId =' + CAST(@db_id as varchar) + ')'
     EXEC (@SQLString)

     --the following creates a new row in the summary table to signify that fragment data is beign captured
     SET @SQLString = 'INSERT INTO systems.dbo.tbl_INDEX_FragData_DB_Summary (intDbId, nvchr_DbName, dte_DataRfshStart) VALUES ('
     SET @SQLString =  @SQLString + CAST(@db_id as varchar) + ', ' + CHAR(39) + @InternalDBName + CHAR(39) + ', GETUTCDATE())'
     EXEC (@SQLString)

     --the following inserts the output from the dmv "dm_db_index_physical_stats" into the detailed table
     INSERT INTO systems.dbo.tbl_INDEX_FragData_Detailed
      (intDbId,  nvchr_DbName, intObject_Id, intIndex_Id, fltFragPrcnt, vchrIndexType, vchrAllocType,
      intUserSeeks, intUserScans, intUserLookups, intUserUpdates, sintPagelocks, sintDisabled, intPageCount)
     SELECT  database_id, DB_NAME(database_id), DMV_IndexStat.[object_id], DMV_IndexStat.[index_id], DMV_IndexStat.[avg_fragmentation_in_percent],
      DMV_IndexStat.[index_type_desc], DMV_IndexStat.alloc_unit_type_desc, 0, 0, 0, 0, 0, 0, DMV_IndexStat.[page_count]
     FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , @ScanMode) AS DMV_IndexStat
     WHERE (DMV_IndexStat.[index_id] > 0) AND DMV_IndexStat.[avg_fragmentation_in_percent] > 0

     --if no rows where inserted then code will "RETURN" out to the parent call or stored proc
     IF @@RowCount = 0
     BEGIN
     --continues out to the nnext stage of the loop
     --the tables used are truncated & the loop counter is decremented

      SET @SQLString = 'UPDATE systems.dbo.tbl_INDEX_FragData_DB_Summary SET dte_DataRfshEnd = GETUTCDATE() WHERE dte_DataRfshEnd IS NULL '
      SET @SQLString =  @SQLString + 'and intDbId = ' + CAST(@db_id as varchar)
      EXEC (@SQLString)

      SET @intInstDbCounter = @intInstDbCounter - 1
     --the continue command is requried to move to the next step ignoring code after this which makes no sesne if no data to process
      CONTINUE
     END

     --This next section retrieves the shcema & table names, also whether table has LOB data
     SET @SQLString = N'UPDATE systems.dbo.tbl_INDEX_FragData_Detailed SET vchr_OwnerName = SubFilt.SchmaName, vchr_TableName = SubFilt.TblName, sintHasLOB = ' +
      N'SubFilt.LobDt FROM systems.dbo.tbl_INDEX_FragData_Detailed JOIN (SELECT SysSchma.[name] SchmaName, SysTbls.[name] TblName, SysTbls.[object_id] ObjctId, ' +
      N'SysTbls.lob_data_space_id LobDt FROM [' + @InternalDBName + N'].sys.tables SysTbls JOIN ['+ @InternalDBName + N'].sys.schemas SysSchma On SysTbls.[schema_id]' +
      N' = SysSchma.[schema_id] WHERE [object_id] IN(SELECT DISTINCT intObject_Id FROM systems.dbo.tbl_INDEX_FragData_Detailed WHERE intDbId = ' + CAST(@db_id as varchar) +
      N')) SubFilt On systems.dbo.tbl_INDEX_FragData_Detailed.intObject_Id = SubFilt.ObjctId WHERE intDbId = ' + CAST(@db_id as varchar)

     EXEC (@SQLString)

     --This removes any system tables which may have been captured
     DELETE
     FROM systems.dbo.tbl_INDEX_FragData_Detailed
     WHERE UPPER(ISNULL(vchr_OwnerName, 'sys')) = 'SYS'

     --This next section retrieves the index name
     SET @SQLString = N'UPDATE systems.dbo.tbl_INDEX_FragData_Detailed SET vchrIndexName = Sys_Indx.[name], sintPagelocks = Sys_Indx.[allow_page_locks], sintDisabled = ' +
      'Sys_Indx.[is_disabled] FROM systems.dbo.tbl_INDEX_FragData_Detailed JOIN [' +  @InternalDBName + N'].sys.indexes As Sys_Indx WITH(NOLOCK) On ' +
      'systems.dbo.tbl_INDEX_FragData_Detailed.intObject_Id = Sys_Indx.[Object_id] AND systems.dbo.tbl_INDEX_FragData_Detailed.intIndex_Id = Sys_Indx.Index_id ' +
      'WHERE intDbId = ' + CAST(@db_id as varchar)

     EXEC (@SQLString)

     --This next section retrieves the Index useage figures
     SET @SQLString = N'UPDATE systems.dbo.tbl_INDEX_FragData_Detailed SET intUserSeeks = DMV_IndexUse.user_seeks, intUserScans = DMV_IndexUse.user_scans, intUserLookups = ' +
      N'DMV_IndexUse.user_lookups, intUserUpdates = DMV_IndexUse.user_seeks FROM systems.dbo.tbl_INDEX_FragData_Detailed JOIN ['+ @InternalDBName + N'].sys.dm_db_index_usage_stats ' +
      N' As DMV_IndexUse ON systems.dbo.tbl_INDEX_FragData_Detailed.intObject_Id = DMV_IndexUse.Object_id AND systems.dbo.tbl_INDEX_FragData_Detailed.intIndex_Id = ' +
      N'DMV_IndexUse.Index_id AND systems.dbo.tbl_INDEX_FragData_Detailed.intDbId = DMV_IndexUse.database_id'

     EXEC (@SQLString)

     SET @SQLString = 'UPDATE systems.dbo.tbl_INDEX_FragData_DB_Summary SET dte_DataRfshEnd = GETUTCDATE() WHERE dte_DataRfshEnd IS NULL '
     SET @SQLString =  @SQLString + 'and intDbId = ' + CAST(@db_id as varchar)
     EXEC (@SQLString)

     SET @intInstDbCounter = @intInstDbCounter - 1
    END

    IF (Object_Id('tempdb..#tbl_InstancePrepDatabases')) Is Not Null Drop Table #tbl_InstancePrepDatabases

    GO

    Saturday, February 15, 2014 5:36 PM

All replies

  • And "PRINT @SQLString" before each EXEC, so that you know which SQL string that causes the error. Then we can talk further.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 7:15 PM
  • What does this script file do? Do I still need above SQLAutoIndexPrepDataCapture for index maintenance?

    Entire job includes 3 steps: This job is to do index maintenance

    1 step. exclude databases never need to be re-indexed

    2 step: this script file I show you above--SQLAutoIndexPrepDataCapture

    3 step: SQLAutoIndexall

    Here is script of 3 step--SQLAutoindexall

    USE [msdb]
    GO

    /****** Object:  StoredProcedure [dbo].[SQLAutoIndexAll]    Script Date: 01/25/2014 22:57:20 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROC .[dbo].[SQLAutoIndexAll]
     @DbName NVARCHAR(128) = 'INSERT_DATABASE_NAME_HERE'  -- Name of the db
     , @ReorgLimit TINYINT = 10  -- Minimum fragmentation % to use Reorg method (used to capture data from DMV's)
     , @RebuildLimit TINYINT = 30 -- Minimum fragmentation % to use Rebuild method
    -- *** If you set the rebuildlimit lower than the reorglimit, then everything captured (above reorglimit) will be rebuilt not reorganised ***
     , @PageLimit SMALLINT = 50  -- Minimum # of Pages before an index will be considered
     , @SortInTempdb TINYINT = 1  -- 1 = Sort in tempdb option, 0 = sort in Db
     , @MaxRunLength INT = 360  -- Maximum number of minutes Proc can run for, this caps allows for abort if time expires
     , @OnLine TINYINT = 0   -- 1 = Online Rebuild, is ignored for Reorg
     , @NewIndexWeight INT = 50  -- the start multipler for first time indexes are defraged instead of days since last defraged
     , @ScanMode VARCHAR(15) = 'SAMPLED' -- LIMITED, SAMPLED, or DETAILED; from fast / non-leaf scan to slow / full B-Tree, only used the data capture where triggered
     , @MailRecipients varchar(255) = 'a@yahoo.com; b@yahoo.com' -- only used during an Error Catch, so details can be captured & invesitigated
    AS

    SET NOCOUNT ON
    --SET DEADLOCK_PRIORITY LOW ;

    DECLARE @SQLString NVARCHAR(2500)
    DECLARE @AbortTime DATETIME

    DECLARE @HeaderMsg varchar(85)
    DECLARE @SMTPResponse smallint
    DECLARE @BodyMsg varchar(2500)

    --the following calculates the requested abort time, please note that this checked after each index is defraged
    --this ensures that at least the worst index is processed prior to abort each time
    SET @AbortTime = DATEADD(mi, @MaxRunLength, GETUTCDATE ())

    IF SERVERPROPERTY('EngineEdition') <> 3 -- Enterprise, EE EVAL or Developer, otherwise disables online ability
    BEGIN
        SET @OnLine = 0 ;
    END

    IF (Object_Id('tempdb..#tbl_TableFragData')) Is Not Null Drop Table #tbl_TableFragData
    --a temp table is used as opposed to table variable to allow for an index to be created, it is also
    --better at handling a greated volume of rows which is possible with the SAP database.
    --yes it could also have been a cursor, but with large vol of rows, even worse!
    CREATE TABLE #tbl_TableFragData
    (int_Frag_RowRef int  IDENTITY(1,1) PRIMARY KEY, --Constraint name not given as this can cause conflicts when used with local temp tables
    intDbId    smallint,
    nvchr_DbName  nvarchar(128),
    vchr_OwnerName  varchar(25),
    vchr_TableName  varchar(75),
    intObject_Id  int,
    vchrIndexName  varchar(250),
    intIndex_Id   int,
    fltFragPrcnt  float(24),
    vchrIndexType  varchar(25),
    vchrAllocType  varchar(25),
    intUserSeeks  int,
    intUserScans  int,
    intUserLookups  int,
    intUserUpdates  int,
    sintHasLOB   smallint,
    sintPagelocks  smallint,
    sintDisabled  smallint,
    sintDaysLstDeFrag smallint,
    fltOrderWeight  float(24),
    intSequence   bigint)

    CREATE INDEX [Ix_Table_#tbl_TableFragData_Col_intSequence]
      ON [dbo].[#tbl_TableFragData](intSequence)

    IF (Object_Id('tempdb..#tbl_InstanceDatabases')) Is Not Null Drop Table #tbl_InstanceDatabases
    --the following table is used to hold the data relating to the Db state, this is also used to control the looping of databases.
    CREATE TABLE #tbl_InstanceDatabases
    (int_InstDb_RowRef int  IDENTITY(1,1) PRIMARY KEY, --Constraint name not given as this can cause conflicts when used with local temp tables
    int_DbId    smallint,
    nvchr_DbName  nvarchar(125))

    CREATE INDEX [Ix_Table_#tbl_InstanceDatabases_Col_int_DbId]
      ON [dbo].[#tbl_InstanceDatabases](int_DbId) INCLUDE (nvchr_DbName)

    --The following section generates the lists of databases to be auto-indexed
    IF @DbName = 'INSERT_DATABASE_NAME_HERE'
    BEGIN
     INSERT INTO #tbl_InstanceDatabases
     (nvchr_DbName, int_DbId)
     SELECT [name], [database_id]
     FROM sys.databases
     WHERE ([state] + is_in_standby + is_read_only) = 0 AND
      [name] NOT IN (SELECT DISTINCT [nvchr_DbName]
         FROM systems..SQLAutoIndex_Exclusions
           UNION
           SELECT 'master'
           UNION
           SELECT 'model'
           UNION
           SELECT 'msdb'
           UNION
           SELECT 'Mssqlresourcedb' --this should not even show up, but for completeness added it to system Db exculsions
           UNION
           SELECT 'tempdb'
           UNION
           SELECT 'systems')
    END
    ELSE
    BEGIN
     INSERT INTO #tbl_InstanceDatabases
     (nvchr_DbName, int_DbId)
     SELECT [name], [database_id]
     FROM sys.databases
     WHERE ([state] + is_in_standby + is_read_only) = 0 AND
      [name] NOT IN (SELECT DISTINCT [nvchr_DbName]
         FROM systems..SQLAutoIndex_Exclusions
           UNION
           SELECT 'master'
           UNION
           SELECT 'model'
           UNION
           SELECT 'msdb'
           UNION
           SELECT 'Mssqlresourcedb' --this should not even show up, but for completeness added it to system Db exculsions
           UNION
           SELECT 'tempdb'
           UNION
           SELECT 'systems') AND
       [name] = @DbName
    END

    DECLARE @intInstDbRowCount smallint
    DECLARE @DteLstIndexGrabStrt datetime
    DECLARE @DteLstIndexGrabEnd datetime

    SET @intInstDbRowCount = (SELECT COUNT(*) FROM #tbl_InstanceDatabases)

    IF @intInstDbRowCount = 0 GoTo Return_And_DropTempTables --This means that there are no databases in this instances which are in a state which allows them to be defraged

    SET @DteLstIndexGrabStrt = (SELECT ISNULL(max(dte_DataRfshStart), 0)
            FROM systems..tbl_INDEX_FragData_DB_Summary
            WHERE dte_DataPurged is NULL)

    --the following check ensures that the index frag rates capture is not more than 7 days old,
    --using this as opposed to a week to compensate for any late running due to contention
    IF @DteLstIndexGrabStrt < DATEADD(dd, -07, GETUTCDATE())
    BEGIN
     EXEC msdb.dbo.SQLAutoIndexPrepDataCapture @ScanMode

     SET @DteLstIndexGrabStrt = (SELECT ISNULL(max(dte_DataRfshStart), 0)
             FROM systems..tbl_INDEX_FragData_DB_Summary
             WHERE dte_DataPurged is NULL)

     IF @DteLstIndexGrabStrt = '1900-01-01 00:00:00.000'
     BEGIN
      GoTo Return_And_DropTempTables   
     END
    END

    SET @DteLstIndexGrabEnd = '1900-01-01 00:00:00.000'

    --the following loop checks that the Index data grab for the specifc database has finished otehrwise it will cycle 2:30 then check again
    --if the time exceeds the @aborttime then if uses a GoTo to the end where the temp tables are dropped
    WHILE @DteLstIndexGrabEnd = '1900-01-01 00:00:00.000'
    BEGIN 
     SET @DteLstIndexGrabEnd = (SELECT ISNULL(max(dte_DataRfshEnd), 0)
           FROM systems..tbl_INDEX_FragData_DB_Summary
           WHERE dte_DataRfshStart = @DteLstIndexGrabStrt)

     IF @DteLstIndexGrabEnd > '1900-01-01 00:00:00.000'
     BEGIN
       BREAK
     END
        ELSE
     BEGIN
      IF GETUTCDATE() > @AbortTime
      BEGIN
       GoTo Return_And_DropTempTables --RETURN
      END
      ELSE
      BEGIN
       WAITFOR DELAY '00:02:30'
       CONTINUE
      END
     END
    END

    --this insert grabs the base fragementation data
    INSERT INTO #tbl_TableFragData
     (intDbId, nvchr_DbName, vchr_OwnerName, vchr_TableName, vchrIndexName, intObject_Id, intIndex_Id, fltFragPrcnt, vchrIndexType, vchrAllocType,
     intUserSeeks, intUserScans, intUserLookups, intUserUpdates, sintHasLOB, sintDaysLstDeFrag, fltOrderWeight, intSequence, sintPagelocks, sintDisabled)
    SELECT intDbId, nvchr_DbName, vchr_OwnerName, vchr_TableName, vchrIndexName, intObject_Id, intIndex_Id, fltFragPrcnt, vchrIndexType,
     vchrAllocType, intUserSeeks, intUserScans, intUserLookups, intUserUpdates, sintHasLOB, @NewIndexWeight , 1, 0, sintPagelocks, sintDisabled
    FROM systems.dbo.tbl_INDEX_FragData_Detailed
    WHERE  intDbId IN
      (SELECT DISTINCT int_DbId FROM #tbl_InstanceDatabases) AND -- Ensures only the relevant databases are de-fraged
     fltFragPrcnt > @ReorgLimit AND -- Ensures that the fragmentation is above a lower limit
     intPageCount >= @PageLimit  -- Ensures that the table covrs a minimum number of pages
    --if no rows where inserted then code will "RETURN" out fo the stored procedure
    IF @@RowCount = 0
    BEGIN

     GoTo Return_And_DropTempTables
    END

    --This next section calculates the days since this index was last defraged
    UPDATE #tbl_TableFragData
    SET sintDaysLstDeFrag = DATEDIFF(day, SubFilt.MaxDte, GETUTCDATE())
    FROM #tbl_TableFragData JOIN
     (SELECT MAX(dteCaptureDateTime) MaxDte, intDbId, intObject_Id, intIndex_Id
     FROM systems..SQL_tbl_INDEX_AutoManage
     GROUP BY intDbId, intObject_Id, intIndex_Id) SubFilt On
    #tbl_TableFragData.intDbId = SubFilt.intDbId AND
    #tbl_TableFragData.intObject_Id = SubFilt.intObject_Id AND
    #tbl_TableFragData.intIndex_Id = SubFilt.intIndex_Id

    --this next update calculates a base mutliplier to be used in calulating an order weight to control the sequence in which defraging is to be performed
    --this is based adding all various seek, scan & lookup counters (which uses the index)
    --then subtracting the update volume which (if the data an index is based is regularly updated then then it can make sense not to defrag)
    --then multiplying this by the number since the index was last defraged, this is help prevent the same indexes being defraged all the time
    --in the event that this mutliplier is zero or negative then the index does not to be defragged at present
    --a zero can either mean the index was just defragged or the index has not had a seek, scan or lookup performed on it
    UPDATE #tbl_TableFragData
    SET fltOrderWeight = (intUserSeeks + intUserScans + intUserLookups - intUserUpdates) * sintDaysLstDeFrag
    WHERE (intUserSeeks + intUserScans + intUserLookups - intUserUpdates) > 0

    --the value previously calculated is then mutlipled by the fragmentation to give a final weight, the higher this figure dictates which indexes will be defraged first
    UPDATE #tbl_TableFragData
    SET fltOrderWeight = fltOrderWeight * fltFragPrcnt * sintDaysLstDeFrag

    UPDATE #tbl_TableFragData
    SET intSequence = RowNmbr
    FROM #tbl_TableFragData JOIN
     (SELECT int_Frag_RowRef, 
      ROW_NUMBER() OVER (ORDER BY fltOrderWeight, intIndex_Id) As RowNmbr
     FROM #tbl_TableFragData) As Ordr_Filt On
    #tbl_TableFragData.int_Frag_RowRef = Ordr_Filt.int_Frag_RowRef

    --the following variables are used to control / within the loop
    DECLARE @intRowCounter int
    DECLARE @FullName  varchar(500)
    DECLARE @IndexName  varchar(250)
    DECLARE @FragPrcnt  float(24)
    DECLARE @IndexTypeDesc varchar(25)
    DECLARE @HasLOB   smallint
    DECLARE @ReOrg0ReIdx1 bit
    DECLARE @Pagelocks  smallint
    DECLARE @Disabled  smallint

    --the counter's start point is set as the max identity value
    SET @intRowCounter = (SELECT MAX(intSequence) FROM #tbl_TableFragData)

    WHILE @intRowCounter > 0
    BEGIN
    --populates the variables from the "active" row. Once again I know that reading in FetchNext from a cursor
    --would work similar, but concerned with potential large cursor overhead
     SET @FullName = (SELECT N'[' + nvchr_DbName + N'].[' + vchr_OwnerName + N'].[' + vchr_TableName + N']' FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)
     SET @IndexName = (SELECT N'[' + vchrIndexName + N']' FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)
     SET @FragPrcnt = (SELECT fltFragPrcnt FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)
     SET @HasLOB = (SELECT sintHasLOB FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)
     SET @IndexTypeDesc = (SELECT vchrIndexType FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)
     
     SET @Pagelocks = (SELECT sintPagelocks FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)
     SET @Disabled = (SELECT sintDisabled FROM #tbl_TableFragData WHERE intSequence = @intRowCounter)

     SET @ReOrg0ReIdx1 = 0

    --checks if Fragmentation Percent is above Rebuild Limit, if not then the Reorganise section will be used
     IF @RebuildLimit < @FragPrcnt
     BEGIN
      SET @ReOrg0ReIdx1 = 1
     END
    --you can only Reorganise an index which is unable to use page locks
     IF @Pagelocks = 0
     BEGIN
      SET @ReOrg0ReIdx1 = 1 
     END
    --you cannot Reorganise a Disabled Index, it makes little sense to Rebuild it hence the continue to the next loop 
     IF @Disabled = 1
     BEGIN
      SET @intRowCounter = @intRowCounter - 1
      CONTINUE
     END
     
    --if 
     IF @ReOrg0ReIdx1 = 1
     BEGIN
      SET @SQLString = N'ALTER INDEX ' + @IndexName + N' ON ' + @FullName + N' REBUILD WITH('
    --Providing that the table does not contain any LOB (Large Object Binary - text, binary, etc) variables
    --and the engine can accomodate online rebuild, thsi will be enabled
      IF @HasLOB = 0 AND @OnLine = 1
      BEGIN
       SET @SQLString = @SQLString + N' ONLINE = ON'
      END
    --will request that rebuild is performed in the tempdb, if the input parameters requests.
    --This quicker, but can fail if the tempdb is not big enough
      SET @SQLString = @SQLString + CASE WHEN @SortInTempdb = 1 THEN N', SORT_IN_TEMPDB = ON ' ELSE N'' END
      SET @SQLString = @SQLString + N') ;'
     END
     ELSE
     BEGIN
      SET @SQLString = N'ALTER INDEX ' + @IndexName + N' ON ' + @FullName + N' REORGANIZE ;' 
     END
    --tidys up & removes any surplus grammar
     SET @SQLString = REPLACE (@SQLString, N'(,', N'(')
     SET @SQLString = REPLACE (@SQLString, N' WITH()', N'')

     -- The actual reorganise / rebuild is performed with a try catch,so that in theroy is one fails,
     --the rest could potentially keep going.
     BEGIN TRY
      EXEC (@SQLString)
    --the index information is written to the tracking table in the systems database
      INSERT INTO systems..SQL_tbl_INDEX_AutoManage
       (intDbId, nvchr_DbName, vchr_OwnerName, vchr_TableName, intObject_Id, vchrIndexName, intIndex_Id, fltFragPrcnt,
       vchrIndexType, vchrAllocType, intUserSeeks, intUserScans, intUserLookups, intUserUpdates, bitReOrg0ReIdx1)
      SELECT intDbId, nvchr_DbName, vchr_OwnerName, vchr_TableName, intObject_Id, vchrIndexName, intIndex_Id, fltFragPrcnt,
       vchrIndexType, vchrAllocType, intUserSeeks, intUserScans, intUserLookups, intUserUpdates,@ReOrg0ReIdx1
      FROM #tbl_TableFragData
      WHERE intSequence = @intRowCounter
    --if the index was reorganised & it is not XML based, will force a full stats update
      IF @ReOrg0ReIdx1 = 0 AND @IndexTypeDesc NOT LIKE N'%XML%'
      BEGIN    
       EXEC('UPDATE STATISTICS ' + @FullName + ' (' + @IndexName + ') WITH FULLSCAN' )
      END
     END TRY
     BEGIN CATCH
      --the email message header & body is set
      SET @HeaderMsg = 'There has been an issue encountered during Index Defrag on ' + CAST(SERVERPROPERTY('ServerName') as varchar)
      SET @BodyMsg = 'SQL Statement = " ' + @SQLString + CHAR(34) + '<br/> Error Number :' + ISNULL(CAST(ERROR_NUMBER() as varchar),' ') + '<br/> Error Severity :' +
         ISNULL(CAST(ERROR_SEVERITY() as varchar), ' ') + '<br/> Error State :' + ISNULL(CAST(ERROR_STATE() as varchar), ' ') + '<br/> Error Procedure :' +
         ISNULL(CAST(ERROR_PROCEDURE() as varchar), ' ') + '<br/> Error Line :' + ISNULL(CAST(ERROR_LINE() as varchar), ' ') + '<br/> Error Message :' +
         ISNULL(CAST(ERROR_MESSAGE() as varchar(250)), ' ') + '<br/> Error captured at ' + CAST(GETUTCDATE() as varchar) + ' UTC Time.'

      --executing the sp_send_dbmail command to physically send the email
      EXEC msdb.dbo.sp_send_dbmail
       @profile_name = 'Auto_Email_Profile',
       @recipients = @MailRecipients, 
       @subject = @HeaderMsg,
       @body = @BodyMsg,
       @body_format = 'HTML';

      SELECT Response = @SMTPResponse
      
     END CATCH ;

     --the following checks if the current system time is greater then the abort tiem set earlier if so
     --then the "RETURN" out of the stored Proc command is issued
     IF GETUTCDATE() > @AbortTime
     BEGIN
      --GoTo Return_And_DropTempTables --RETURN
      BREAK
     END
    --the loop counter for the tables is decremented
     SET @intRowCounter = @intRowCounter - 1
    END

    Return_And_DropTempTables:

    --drop temp tables, yes should drop anyway when connection terminated, but for tidyness drop regardless
    IF (Object_Id('tempdb..#tbl_TableFragData')) Is Not Null Drop Table #tbl_TableFragData
    IF (Object_Id('tempdb..#tbl_InstanceDatabases')) Is Not Null Drop Table #tbl_InstanceDatabases

    RETURN


    GO

    Saturday, February 15, 2014 7:29 PM
  • Very likely, the error is in one of your EXEC(@SQLString).  While you are trying to get this to work, I would put a PRINT @SQLString immediately before each of the EXEC(@SQLString) commands.  That will get you two things, you will see which dynamic command is causing the failure and since you will have the command that is failing displayed, you can often easily see the problem quickly.  I almost always do that while developing dynamic SQL.  When the procedure is working, just comment out the PRINT commands.  That leaves them there but not being executed, so it is easy to uncomment them if they are ever needed again.

    Also, it is not the cause of your problem here, but you are doing cast(... as varchar).  It is best practice to always specify the length.  If you do not, you get the default value.  Depending on exactly what you are doing the default can be 1, and it can be 50.  It is better for documentation.

    Tom

    Saturday, February 15, 2014 7:33 PM
  • Do  you mean  probably something wrong with " DECLARE @SQLString NVARCHAR(2500)"? if we "PRINT @SQLString", we will find the actual length of @SQLString ?Or PRINT @SQLString will generate any error message?

    or probably something wrong with "WHERE intDbId =' + CAST(@db_id as varchar)"?

    Anything possible need to be changed for "CAST(@db_id as varchar)"?

    What does "CAST(@db_id as varchar)" mean ? I have "DECLARE @db_id BIGINT" at the beginning...I cannot make @db_int bigger as intger...


    Please be more specific,thanks




    • Edited by bestrongself Saturday, February 15, 2014 8:34 PM
    Saturday, February 15, 2014 8:16 PM
  • What does this script file do?

    Dunno. It's your script, not mine.

    Do I still need above SQLAutoIndexPrepDataCapture for index maintenance?

    Dunno, but since you have problems running it, I guess you find a good reason to run it.

    Then again, I get a little worried that you run scripts that you don't know what they are doing.

    Do  you mean  probably something wrong with " DECLARE @SQLString NVARCHAR(2500)"? if we "PRINT @SQLString", we will find the actual length of @SQLString ?Or PRINT @SQLString will generate any error message?

    "PRINT @SQLString" will print the contents of the variable @SQLString. This is needed to that you know which of the generated SQL statement that bombs.

    or probably something wrong with "WHERE intDbId =' + CAST(@db_id as varchar)"?

    Anything possible to change for "CAST(@db_id as varchar)"?

    Look, neither Tom or I have absolutely no idea of what is wrong in your script. What we are doing is to help you to diagnose the error. If we happen to spot a possible source for error, we may point that out. This is not the same as say as that is the error. Because, as I said, we have no clue.

    In this particular case, "CAST(@db_id as varchar)" is the same as "CAST(@db_id as varchar(30))" and since a database id is never bigger than 32767 nothing can go wrong. But there might be other places in the script this may cause problems.

    As I recall, you have been asking about this procedure before, and at that time I concluded that you had an unfortunate mix of int and bigint. Supposedly you have one left. Where? I don't know, and I'm not spending a Saturday night looking the code from to bottom when you can add PRINT statements to find out which statement that fails.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 8:45 PM
  • Do  you mean  probably something wrong with " DECLARE @SQLString NVARCHAR(2500)"? if we "PRINT @SQLString", we will find the actual length of @SQLString ?Or PRINT @SQLString will generate any error message?

    or probably something wrong with "WHERE intDbId =' + CAST(@db_id as varchar)"?

    Anything possible need to be changed for "CAST(@db_id as varchar)"?

    What does "CAST(@db_id as varchar)" mean ? I have "DECLARE @db_id BIGINT" at the beginning...I cannot make @db_int bigger as intger...


    Please be more specific,thanks




    If you do a PRINT @SQLString it will output the contents of @SQLString.  That is the command you are executing when you do EXEC(@SQLString).  If you show us the output of the last print statement before you get the error and give us the table structure (columns and datatypes) of any of your tables used in that command, we can probably help you find the problem.

    Instead of doing CAST(@db_id as varchar), it is best practice to do CAST(@db_id as varchar(20)).  I picked 20 because that's the longest possible result when you convert a bigint to a varchar.  In general when declaring varchar columns, variables or casting a value to a varchar you want to specify the longest valid value for that particular piece of data.  But as I say, that;s just best case practice, it is not the cause of the problem you;re having here.

    Tom

    Saturday, February 15, 2014 8:52 PM
  • I will And "PRINT @SQLString" before each EXEC and then re-run script to re-create store procedure SQLAutoIndexPrepDataCapture.

    Then run store procedure  SQLAutoIndexPrepDataCapture.

    It will list all @string and generate error message if one exec(@string) fails.

    Correct?

    Error log for the first "SQLAutoIndexPrepDataCapture" script:

    Job '_SQL_AutoIndexAll' : Step 2, 'SQLAutoIndexPrepDataCapture' : Began Executing 2014-02-16 21:40:47

    Msg 8115, Sev 16, State 2, Line 1 : Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003]
    Msg 3621, Sev 16, State 1, Line 1 : The statement has been terminated. [SQLSTATE 01000]

    It looks something wrong with Line 1 in script. Which line is line 1?I want to find int there and make int become big int

    Thanks

    Saturday, February 15, 2014 9:10 PM