?
Solved

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

Posted on 2013-05-20
4
Medium Priority
?
546 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 2000 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

771 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