Solved

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

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

803 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