Ben Seaman on Thu, 17 Jan 2013 17:02:05

What does dbo.IndexOptimize actually return? If it's a lot of data perhaps it is causing an overflow in the max size of an email message body size. Have you tried using the @attach_query_result_as_file option set to 1?

Eben Raja Earnest on Thu, 17 Jan 2013 18:34:26

Actually this particular procedure was taken from an forum some 3 years before and the SP code is as below and the o/p will be something typical as below

Since am stumped not tomakechanges to the way the maintenance runs , except for if we can change the SP to dump data into an temp table and have that table o/p sent as notepad , something like that .

Anway am also going to check what will happen if i change@attach_query_result_as_file option set to 1

Please check the output of the index rebuild process as follows:

DateTime: 2013-01-13 16:16:00

Procedure: [ABC].[IndexOptimize]

Parameters: @Databases = 'ABC', @FragmentationHigh_LOB = 'NOTHING', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE', @FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE', @FragmentationLow_LOB = 'NOTHING',

@FragmentationLow_NonLOB = 'NOTHING', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000

 

DateTime: 2013-01-13 16:16:00

Database: [ABC]

Status: ONLINE

 

Updating [dbo].[tblShipmentSplit]

    [PK_tblShipmentSplit], update is not necessary...

    [_WA_Sys_splSplittedID_015F0FBB], update is not necessary...

    [IX_tblShipmentSplit_splShpID], update is not necessary...

    [IX_tblShipmentSplit_splSplittedID], update is not necessary...

    0 index(es)/statistic(s) have been updated, 4 did not require update.

this will go on for each of the table that meets the criteria.;

Below is the Stored procedure.

----------------------------------------------------------------------------------------

USE [ABC]
GO
/****** Object:  StoredProcedure [dbo].[IndexOptimize]    Script Date: 01/17/2013 18:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IndexOptimize]
@Databases varchar(max),
@FragmentationHigh_LOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationLow_LOB varchar(max) = 'NOTHING',
@FragmentationLow_NonLOB varchar(max) = 'NOTHING',
@FragmentationLevel1 tinyint = 5,
@FragmentationLevel2 tinyint = 30,
@PageCountLevel int = 1000
AS
SET NOCOUNT ON
----------------------------// Declare variables //-- -----------------------------------
DECLARE @dbid INT
DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)
DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentCommandSelect01 varchar(max)
DECLARE @CurrentCommandSelect02 varchar(max)
DECLARE @CurrentCommandSelect03 varchar(max)
DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommand02 varchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput02 int
DECLARE @CurrentIxID int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName varchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName varchar(max)
DECLARE @CurrentIndexID int
DECLARE @CurrentIndexName varchar(max)
DECLARE @CurrentIndexType int
DECLARE @CurrentIndexExists bit
DECLARE @CurrentIsLOB bit
DECLARE @CurrentFragmentationLevel float
DECLARE @CurrentPageCount bigint
DECLARE @CurrentAction varchar(max)
DECLARE @CurrentComment varchar(max)
DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY, DatabaseName varchar(max), Completed bit)
DECLARE @tmpIndexes TABLE ( IxID int IDENTITY PRIMARY KEY, SchemaID int, SchemaName varchar(max), ObjectID int, ObjectName varchar(max), IndexID int, IndexName varchar(max), IndexType int, Completed bit)
DECLARE @tmpIndexExists TABLE ([Count] int)
DECLARE @tmpIsLOB TABLE ([Count] int)
DECLARE @Actions TABLE ([Action] varchar(max))
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
INSERT INTO @Actions([Action]) VALUES('STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE_STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('NOTHING')
DECLARE @Error int
SET @Error = 0
----------------------------// Log initial information //-- -----------------------------
SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_LOB = ' + ISNULL('''' + @FragmentationHigh_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_NonLOB = ' + ISNULL('''' + @FragmentationHigh_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_LOB = ' + ISNULL('''' + @FragmentationMedium_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_NonLOB = ' + ISNULL('''' + @FragmentationMedium_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_LOB = ' + ISNULL('''' + @FragmentationLow_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_NonLOB = ' + ISNULL('''' + @FragmentationLow_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS varchar),'NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
---------------------------// Select databases //-- ---------------------------------------
IF @Databases IS NULL OR @Databases = ''
BEGIN
  SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END

INSERT INTO @tmpDatabases (DatabaseName, Completed)

SELECT @Databases AS DatabaseName, 0 AS Completed
/******** Can be commented ***********/
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC
/******** Can be commented ***********/
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
  SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
---------------------------// Check input parameters //-- ----------------------------------
IF @FragmentationHigh_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationHigh_LOB is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationHigh_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationHigh_NonLOB is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationMedium_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationMedium_LOB is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationMedium_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationMedium_NonLOB is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationLow_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationLow_LOB is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationLow_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationLow_NonLOB is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL
BEGIN
  SET @ErrorMessage = 'The value for parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT SET @Error = @@ERROR
END
IF @PageCountLevel < 0 OR @PageCountLevel IS NULL
BEGIN
  SET @ErrorMessage = 'The value for parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10)
  RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
  SET @Error = @@ERROR
END
-----------------------------// Check error variable //-- ------------------------------------------
IF @Error <> 0 GOTO Logging
-------------------------- --// Execute commands //-- ----------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN
  SELECT TOP 1 @CurrentID = ID, @CurrentDatabase = DatabaseName FROM @tmpDatabases WHERE Completed = 0 ORDER BY ID ASC
  -- Set database message
  SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
  SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
  SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(13) + CHAR(10)
  RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
  IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
  BEGIN
    -- Select indexes in the current database
    SET @CurrentCommandSelect01 = 'SELECT sys.schemas.[schema_id], sys.schemas.[name], sys.objects.[object_id], sys.objects.[name], sys.indexes.index_id, sys.indexes.[name], sys.indexes.[type], 0 FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.objects.is_ms_shipped = 0 AND sys.indexes.[type] IN(1,2) ORDER BY sys.schemas.[schema_id] ASC, sys.objects.[object_id] ASC, sys.indexes.index_id ASC'
    INSERT INTO @tmpIndexes (SchemaID, SchemaName, ObjectID, ObjectName, IndexID, IndexName, IndexType, Completed)
    EXECUTE(@CurrentCommandSelect01)
    WHILE EXISTS (SELECT * FROM @tmpIndexes WHERE Completed = 0)
    BEGIN
      SELECT TOP 1 @CurrentIxID = IxID, @CurrentSchemaID = SchemaID, @CurrentSchemaName = SchemaName, @CurrentObjectID = ObjectID, @CurrentObjectName = ObjectName, @CurrentIndexID = IndexID, @CurrentIndexName = IndexName, @CurrentIndexType = IndexType FROM @tmpIndexes WHERE Completed = 0 ORDER BY IxID ASC
      -- Does the index exist?
      SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.indexes.index_id > 0 AND sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS varchar) + ' AND sys.schemas.[name] = ''' + @CurrentSchemaName + ''' AND sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.objects.[name] = ''' + @CurrentObjectName + ''' AND sys.indexes.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND sys.indexes.[name] = ''' + @CurrentIndexName + ''' AND sys.indexes.[type] = ' + CAST(@CurrentIndexType AS varchar)
      INSERT INTO @tmpIndexExists ([Count]) EXECUTE(@CurrentCommandSelect02)
      IF (SELECT [Count] FROM @tmpIndexExists) > 0
      BEGIN
        SET @CurrentIndexExists = 1
      END
      ELSE
      BEGIN
        SET @CurrentIndexExists = 0
      END
      IF @CurrentIndexExists = 0
      GOTO NoAction
      -- Does the index contain a LOB?
      IF @CurrentIndexType = 1
        SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND (sys.types.name IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.name IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.columns.max_length = -1))'
      IF @CurrentIndexType = 2
        SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.columns ON sys.index_columns.[object_id] = sys.columns.[object_id] AND sys.index_columns.column_id = sys.columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.index_columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.index_columns.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND (sys.types.[name] IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.[name] IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.types.max_length = -1))'
        INSERT INTO @tmpIsLOB ([Count]) EXECUTE(@CurrentCommandSelect03)
      IF (SELECT [Count] FROM @tmpIsLOB) > 0
      BEGIN
        SET @CurrentIsLOB = 1
      END
      ELSE
      BEGIN
        SET @CurrentIsLOB = 0
      END
      -- Is the index fragmented?
      SET @dbid = DB_ID(@CurrentDatabase);
      SELECT @CurrentFragmentationLevel = avg_fragmentation_in_percent, @CurrentPageCount = page_count
      FROM sys.dm_db_index_physical_stats(@dbid, @CurrentObjectID, @CurrentIndexID, NULL, 'LIMITED')
      WHERE alloc_unit_type_desc = 'IN_ROW_DATA' AND index_level = 0
      -- Decide action
      SELECT @CurrentAction = CASE WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_LOB
        WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_NonLOB
        WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_LOB
        WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_NonLOB
        WHEN @CurrentIsLOB = 1 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_LOB
        WHEN @CurrentIsLOB = 0 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_NonLOB
        END
      -- Create comment
      SET @CurrentComment = 'IndexType: ' + CAST(@CurrentIndexType AS varchar) + ', '
      SET @CurrentComment = @CurrentComment + 'LOB: ' + CAST(@CurrentIsLOB AS varchar) + ', '
      SET @CurrentComment = @CurrentComment + 'PageCount: ' + CAST(@CurrentPageCount AS varchar) + ', '
      SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + CAST(@CurrentFragmentationLevel AS varchar)
      IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE','INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE')
      BEGIN
        SELECT @CurrentCommand01 = CASE WHEN @CurrentAction = 'INDEX_REBUILD_ONLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'
        WHEN @CurrentAction = 'INDEX_REBUILD_OFFLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF)'
        WHEN @CurrentAction IN('INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE') THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REORGANIZE'
        END
        EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, @CurrentComment, 2
        SET @Error = @@ERROR
        IF @ERROR <> 0
        BEGIN
          SET @CurrentCommandOutput01 = @ERROR
        END
      END
      IF @CurrentAction IN('INDEX_REORGANIZE_STATISTICS_UPDATE','STATISTICS_UPDATE')
      BEGIN
        SET @CurrentCommand02 = 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentIndexName)
        EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, @CurrentComment, 2
        SET @Error = @@ERROR
        IF @ERROR <> 0
        BEGIN
          SET @CurrentCommandOutput02 = @ERROR
        END
      END

    NoAction: -- Update that the index is completed
    UPDATE @tmpIndexes SET Completed = 1
    WHERE IxID = @CurrentIxID -- Clear variables
    SET @CurrentCommandSelect02 = NULL
    SET @CurrentCommandSelect03 = NULL
    SET @CurrentCommand01 = NULL
    SET @CurrentCommand02 = NULL
    SET @CurrentCommandOutput01 = NULL
    SET @CurrentCommandOutput02 = NULL
    SET @CurrentIxID = NULL
    SET @CurrentSchemaID = NULL
    SET @CurrentSchemaName = NULL
    SET @CurrentObjectID = NULL
    SET @CurrentObjectName = NULL
    SET @CurrentIndexID = NULL
    SET @CurrentIndexName = NULL
    SET @CurrentIndexType = NULL
    SET @CurrentIndexExists = NULL
    SET @CurrentIsLOB = NULL
    SET @CurrentFragmentationLevel = NULL
    SET @CurrentPageCount = NULL
    SET @CurrentAction = NULL
    SET @CurrentComment = NULL
    DELETE FROM @tmpIndexExists
    DELETE FROM @tmpIsLOB
    END
  END
  -- Update that the database is completed
  UPDATE @tmpDatabases
  SET Completed = 1 WHERE ID = @CurrentID
  -- Clear variables
  SET @CurrentID = NULL
  SET @CurrentDatabase = NULL
  SET @CurrentCommandSelect01 = NULL
  DELETE FROM @tmpIndexes
END

EXEC sp_updatestats

--------------------------------// Log completing information //-- --------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)
RAISERROR(@EndMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------

Tom Phillips on Thu, 17 Jan 2013 19:14:16

What do you get when you run this:

DECLARE @query varchar(max) 
SET  @query = 'EXEC ABC.dbo.IndexOptimize @Databases = ''ABC'', 
                            @FragmentationHigh_LOB = ''NOTHING'',   
                            @FragmentationHigh_NonLOB = ''NOTHING'',   
                            @FragmentationMedium_LOB = ''INDEX_REORGANIZE'',  
                            @FragmentationMedium_NonLOB = ''INDEX_REORGANIZE'', 
                            @FragmentationLow_LOB = ''NOTHING'', 
                            @FragmentationLow_NonLOB = ''NOTHING'', 
                            @FragmentationLevel1 = 5, 
                            @FragmentationLevel2 = 30, 
                            @PageCountLevel = 1000'
EXEC (@query)

Eben Raja Earnest on Thu, 17 Jan 2013 20:17:33

If i issue the same on query analyzer, yes it executes and i can see messages flowing, but since i don't have an downtime i cant have it run for an hour now to get the results.

Last time when i ran using the same old T-SQL that i pasted on my first post directly from my query analyzer, it did ran for 55 minutes and bumped out with error , which was difficult at least for me to find what was wrong. attaching the error

One more thing was we see this happen after we established Mirroring on this DB, however we have and staging env were mirroring is present on this same db and still reorganise works there and email comes.

Shall i go forward and change the T-SQL of the job with the query you have given meas below and see if it will function when it runs on its schedule time ?

am not good in scripting hence request to know if the below is correct.

EXEC msdb.dbo.sp_send_dbmail
          @profile_name = 'ABC’,
          @recipients = 'CENSYSGSCAPP@xxx.com;Team@xx.com;xxxx@sss.com;xxx@sss.com;xxx@aa.com',  
          @subject = 'Daily index reorganization process is finished on DB Pord Server',
          @body = 'Please check the output of the index rebuild process as follows: ',
DECLARE @query varchar(max)
SET  @query = 'EXEC ABC.dbo.IndexOptimize @Databases = ''ABC'',
                            @FragmentationHigh_LOB = ''NOTHING'',   
                            @FragmentationHigh_NonLOB = ''NOTHING'',   
                            @FragmentationMedium_LOB = ''INDEX_REORGANIZE'',  
                            @FragmentationMedium_NonLOB = ''INDEX_REORGANIZE'',
                            @FragmentationLow_LOB = ''NOTHING'',
                            @FragmentationLow_NonLOB = ''NOTHING'',
                            @FragmentationLevel1 = 5,
                            @FragmentationLevel2 = 30,
                            @PageCountLevel = 1000'
EXEC (@query)

Eben Raja Earnest on Thu, 17 Jan 2013 20:18:16

set option to 1 doesn't work , am getting same error message,.

Ben Seaman on Fri, 18 Jan 2013 11:11:40

Can you right-click on the job in SSMS and script it and post the script?

Eben Raja Earnest on Fri, 18 Jan 2013 12:20:01

USE [msdb]
GO
/****** Object:  Job [IndexReorganize_daily]    Script Date: 01/18/2013 12:16:26 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/18/2013 12:16:26 ******/
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'IndexReorganize_daily',
        @enabled=1,
        @notify_level_eventlog=2,
        @notify_level_email=2,
        @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',
        @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [IndexReorganize_SendMail]    Script Date: 01/18/2013 12:16:27 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IndexReorganize_SendMail',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=4,
        @on_success_step_id=2,
        @on_fail_action=4,
        @on_fail_step_id=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'EXEC msdb.dbo.sp_send_dbmail
          @profile_name = ''Singh'',
          @recipients = ''CENSYSGSCAPP@xyz.com;SqlDBATeam@xx.com;bo@xs.com;ws@xs.com;ash@xs.com'',
          @subject = ''Daily index reorganization process is finished on DB MWF Pord Server'',
          @body = ''Please check the output of the index rebuild process as follows: '',
          @query = ''EXEC ABC.dbo.IndexOptimize @Databases = ''''ABC'''',
                            @FragmentationHigh_LOB = ''''NOTHING'''',   
                            @FragmentationHigh_NonLOB = ''''NOTHING'''',   
                            @FragmentationMedium_LOB = ''''INDEX_REORGANIZE'''',  
                            @FragmentationMedium_NonLOB = ''''INDEX_REORGANIZE'''',
                            @FragmentationLow_LOB = ''''NOTHING'''',
                            @FragmentationLow_NonLOB = ''''NOTHING'''',
                            @FragmentationLevel1 = 5,
                            @FragmentationLevel2 = 30,
                            @PageCountLevel = 1000'',
          @attach_query_result_as_file = 0',
        @database_name=N'msdb',
        @output_file_name=N'D:\IndexReorganizeLog\ReOrganizeLog_daily.txt',
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [MDWSMF Reorganize]    Script Date: 01/18/2013 12:16:27 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'MDWSMF Reorganize',
        @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 msdb.dbo.sp_send_dbmail
          @profile_name = ''Singh'',
          @recipients = ''CENSYSGSCAPP@xyz.com;SqlDBATeam@xx.com;bo@xs.com;ws@xs.com;ash@xs.com'',
          @subject = ''Daily index reorganization process is finished on DB MDWSMF Pord Server'',
          @body = ''Please check the output of the index rebuild process as follows: '',
          @query = ''EXEC DEFGF.dbo.IndexOptimize @Databases = ''''DEFG'''',
                            @FragmentationHigh_LOB = ''''NOTHING'''',   
                            @FragmentationHigh_NonLOB = ''''NOTHING'''',   
                            @FragmentationMedium_LOB = ''''INDEX_REORGANIZE'''',  
                            @FragmentationMedium_NonLOB = ''''INDEX_REORGANIZE'''',
                            @FragmentationLow_LOB = ''''NOTHING'''',
                            @FragmentationLow_NonLOB = ''''NOTHING'''',
                            @FragmentationLevel1 = 5,
                            @FragmentationLevel2 = 30,
                            @PageCountLevel = 1000'',
          @attach_query_result_as_file = 0',
        @database_name=N'msdb',
        @output_file_name=N'D:\IndexReorganizeLog\MDWSMF Reorganize.txt',
        @flags=2
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'IndexReorganize_daily',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20080721,
        @active_end_date=99991231,
        @active_start_time=190000,
        @active_end_time=235959
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:

Eben Raja Earnest on Sun, 20 Jan 2013 10:27:43

Tried this query and got the same error

Job 'test reorganise' : Step 1, 'Step1:' : Began Executing 2013-01-18 20:15:00

Msg 22050, Sev 16, State 1: Error formatting query, probably invalid parameters [SQLSTATE 42000]

Mohammad Nizamuddin on Sun, 20 Jan 2013 15:24:43

Hi,

You can try this code, i hope this will work, to get the entire code click on link below

Script For Setting Database Mail and Sending Email From SQL Server 2008

/*Run this script after setting proper configurations*/ 

-- ENABLE DATABASE MAIL FEATURE IN SYSTEM CONFIGURATION

use master

go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

go

reconfigure

go 

-- DECLARE DATABASE MAIL ACCOUNT PARAMETERS

Declare @qmail_account_name as varchar(64);

Declare @qmail_account_description as varchar(64);

Declare @qmail_account_email_address as varchar(64);

Declare @qmail_account_display_name as varchar(64);

Declare @qmail_account_username as varchar(64);

Declare @qmail_account_password as varchar(64);

Declare @qmail_account_mailserver_name as varchar(64);

 

-- DECLARE PROFILE PARAMETERS

declare @qmail_profile_profile_name as varchar(64);

declare @qmail_profile_description as varchar(64);

-- DECLARE PRINCIPLES

declare @qprincipal_name as varchar(16);

-- SET SMTP ACCOUNT DETAILS, USERNAME AND PASSWORD HERE

set @qmail_account_mailserver_name = 'smtp.bizmail.yahoo.com' -- your smtp address

set @qmail_account_username='something@something.com'  -- username

set @qmail_account_email_address = 'myEmailName@something.com' -- email address

set @qmail_account_password='password for user (something@something.com)'  -- password for username

set @qmail_account_name = 'YourAccountName (e.g MyAppName_EmailAccount)'

set @qmail_account_description = 'Mail account for administrative e-mail.'

set @qmail_account_display_name = 'MyApplicationName Automated Mailer'

-- PROFILE PARAMETERS ASSIGNMENT

set  @qmail_profile_profile_name = 'MyAppNameEmailServiceProfile'

set  @qmail_profile_description = 'Profile used for database mail jobs'

 

--PROFILE TYPE

set @qprincipal_name= 'public'

if (      @qmail_account_mailserver_name = '' or

@qmail_account_username=''  or 

@qmail_account_email_address = ''  or @qmail_account_password='' )

begin

   Select 'Please enter SMTP details' as [Comments]

end

else

Begin

                  if exists(select * from  msdb.dbo.sysmail_account where[name]=@qmail_account_name)

                  begin

                  exec msdb.dbo.sysmail_delete_account_sp @account_name =@qmail_account_name

                  end

 

                  EXECUTE msdb.dbo.sysmail_add_account_sp

                    @account_name = @qmail_account_name,

                        @description = @qmail_account_description,

                        @email_address = @qmail_account_email_address,

                        @display_name = @qmail_account_display_name,

                        @username=@qmail_account_username,

                        @password=@qmail_account_password,

                        @mailserver_name = @qmail_account_mailserver_name

 

                  if exists(select * from  msdb.dbo.sysmail_profile where[name]=@qmail_profile_profile_name)

                  begin

                  exec msdb.dbo.sysmail_delete_profile_sp @profile_name =@qmail_profile_profile_name

                  end

 

                  EXECUTE msdb.dbo.sysmail_add_profile_sp

                           @profile_name = @qmail_profile_profile_name,

                           @description = @qmail_profile_description

 

                  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

                        @profile_name = @qmail_profile_profile_name,

                        @account_name = @qmail_account_name,

                        @sequence_number = 1

 

                  EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

                        @profile_name = @qmail_profile_profile_name,

                        @principal_name = @qprincipal_name,

                        @is_default = 1 ;

end

 

Step2: Create a Sproc which will send email notification. Below is the script for that only you need to change your email To/CC, Subject, Body from your application tables or you can set anything you want. Please make sure Email profile name is proper it should be same as  you have configured during Database Email Setup.

 if exists (select 1 from sys.objects where name = 'p_SendEmailNotification' andtype = 'p')

drop procedure p_SendEmailNotification

go

 

Create Procedure [dbo].[p_SendEmailNotification]

as

Begin

Declare @ErrorCode as int 

BEGIN TRANSACTION 

declare @MailPriority varchar(8);

declare @sendAddressTo varchar (256);

declare @sendAddressCC varchar(256);

declare @MailBodyFormat varchar(8);

declare @DBMailProfile varchar(32);

 

set @MailBodyFormat ='HTML'

set @DBMailProfile = 'Email Profile Name which you have created during DB Mail setup (e.g MyAppNameEmailServiceProfile)'

set @MailPriority = 'Normal'

 

select @sendAddressTo = 'someone@someone.com,abc@abc.com'

 

select @sendAddressCC ='someone@someone.com,xyz@xyz.com'

 

EXEC msdb.dbo.sp_send_dbmail

                        @profile_name = @DBMailProfile,

                        @recipients=@sendAddressTo,

                        @copy_recipients  = @sendAddressCC,

                        @importance = @MailPriority,

                        @subject = 'Email Subject',

                        @body = 'Email Body....hi this is test email',

                        @body_format = @MailBodyFormat ;

                       

 set @ErrorCode = @@ERROR 

 if(@ErrorCode <> 0) 

 begin  

  goto ERR_HANDLER  

 end

 

 COMMIT TRANSACTION   

Set @ErrorCode = @@Error 

if @ErrorCode<>0 begin goto ERR_HANDLER end 

 

ERR_HANDLER: 

if @ErrorCode<>0 

Begin 

 ROLLBACK TRANSACTION 

End

End

Eben Raja Earnest on Mon, 21 Jan 2013 05:00:02

If you can see, i had scripted out the job, there are 2 steps both entitled to send an email, and both of them using the same profile.

Now when the second step is able to run and send the email successfully, why is the first step failing after running for 60 minutes  and with that error formatting query.

BR

Eben

Mohammad Nizamuddin on Mon, 21 Jan 2013 05:56:58

If you can see, i had scripted out the job, there are 2 steps both entitled to send an email, and both of them using the same profile.

Now when the second step is able to run and send the email successfully, why is the first step failing after running for 60 minutes  and with that error formatting query.

BR

Eben

What I will suggest is :-

1) In your first step when it is executing the query, in the query add sql code to write messages (just for debugging purpose) at almost all steps, latter you can comment this and see where exactly it is failing.....

2) Also check msdb database for email processing tables, just query it and see whether a record is created in this the table or not