Solved

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

Posted on 2013-05-20
4
513 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
  • 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now