Solved

How to use SQL agent steps to retry job after an error

Posted on 2013-05-20
4
542 Views
Last Modified: 2013-08-20
Hi,
I am running script below to rebuild fragmented indexes. I have a badly written application and I have to run this SQL every single day to make sure the application performs well.

I have simple question.. how do I use agent steps option so that job is retried if it comes back with an error. I can use sp_sendmail to raise an alert  to know job failed.

Please do not advise  on altering application as it's beyond my control.

SET  NOCOUNT ON
DECLARE @dbName   NVARCHAR (128)
DECLARE @SchemaName   NVARCHAR (128)
DECLARE @TableName   NVARCHAR (128)
DECLARE @IndexName   NVARCHAR (128)
DECLARE @avg_fragmentation_in_percent   FLOAT
DECLARE @nSQL   NVARCHAR (4000)
DECLARE @index_list TABLE (
                       dbName NVARCHAR (128),
                       SchemaName NVARCHAR (128),
                       TableName NVARCHAR (128),
                       IndexName NVARCHAR (128),
                       avg_fragmentation_in_percent FLOAT
                    )
INSERT INTO @index_list
   SELECT DB_NAME (PS.database_id) AS dbName,
          S.name AS SchemaName,
          O.name AS TableName,
          b.name,
          ps.avg_fragmentation_in_percent
     FROM          sys.dm_db_index_physical_stats (DB_ID (),
                                                   NULL,
                                                   NULL,
                                                   NULL,
                                                   NULL
                   ) AS ps
                INNER JOIN
                   sys.indexes AS b
                ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
             INNER JOIN
                sys.objects O
             ON PS.object_id = O.object_id
          INNER JOIN
             sys.schemas S
          ON S.schema_id = O.schema_id
    WHERE ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
                                               AND PS.index_type_desc IN ('CLUSTERED INDEX', 'NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
                                                                                                                  AND b.is_hypothetical = 0 -- Only real indexes
                                                                                                                                           AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
                                                                                                                                                                         AND PS.page_count > 8 --- ignore tables less tha 64K
                                                                                                                                                                         AND O.name not like '%wk%'

--ORDER BY ps.avg_fragmentation_in_percent DESC

SELECT TOP 1
       @dbName                         = dbName,
       @SchemaName                     = SchemaName,
       @TableName                      = TableName,
       @IndexName                      = IndexName,
       @avg_fragmentation_in_percent   = avg_fragmentation_in_percent
FROM @index_list

WHILE (@@rowcount <> 0)
   BEGIN
      IF @avg_fragmentation_in_percent <= 40                     -- REORGANIZE
         SET @nSQL   =
                  'ALTER INDEX '
                + @IndexName
                + ' ON '
                + @dbName
                + '.'
                + @SchemaName
                + '.'
                + @TableName
                + ' REORGANIZE;'
      ELSE                                                          -- REBUILD
         SET @nSQL   =
                  'ALTER INDEX '
                + @IndexName
                + ' ON '
                + @dbName
                + '.'
                + @SchemaName
                + '.'
                + @TableName
                + ' REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON, MAXDOP = 0);'
      BEGIN TRY
EXECUTE (@nSQL);
      END TRY
      BEGIN CATCH
         SET @nSQL   = REPLACE (@nSQL, 'ONLINE = ON,', '')

EXECUTE (@nSQL);
      END CATCH

      --PRINT @nSQL

      DELETE FROM @index_list
       WHERE     dbName = @dbName
             AND SchemaName = @SchemaName
             AND TableName = @TableName
             AND IndexName = @IndexName
             AND avg_fragmentation_in_percent = @avg_fragmentation_in_percent

      SELECT TOP 1
             @dbName                         = dbName,
             @SchemaName                     = SchemaName,
             @TableName                      = TableName,
             @IndexName                      = IndexName,
             @avg_fragmentation_in_percent   = avg_fragmentation_in_percent
      FROM @index_list
   END
0
Comment
Question by:crazywolf2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39183031
Hi

Recommend you try Ola Hallengren's free script, which does what you want, and possibly recovers better than your script.

Regards
  David
0
 
LVL 23

Assisted Solution

by:Ioannis Paraskevopoulos
Ioannis Paraskevopoulos earned 500 total points
ID: 39183679
Hi,

Please have a look at the attached. You need to define the number of retries before a failure is reported and the interval (in mins) between retries. For instance if you set the retries to 2 and the interval to 5 it means that after a failure it will wait for 5 mins and then it will try again. Then if it fails again, it will wait another five mins and will try again. As it is set to retry only two times, if it fails once more than the job will fail and it will report a failure (if it is set to report a failure on fail)

Giannis
Retry.png
0
 

Author Comment

by:crazywolf2010
ID: 39222266
Hi,
How does failure of a step is determined?

Thanks
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 39222428
Hi,

It depends on the kind of the step. For instance a violation of a primary key is determined by SQL. A vbscript execution may or may not have a return code. If your step is an SQL query, you may rest adure that it will know if it was executed succesfuly or not.

Giannis
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question