Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
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

610 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