none
Trying to make Automation automatically rebuild all tables

    Question

  • Using the script found in the "Fragmentation in SQL Azure!" blog post, I created a runbook that will automatically rebuild indexes in a SQL Azure database. It works fine with smaller databases but it fails on larger ones. I get this exception from Automation:

    The job cannot continue running because it was repeatedly evicted from the same checkpoint. Please make sure your Runbook does not perform lengthy operations without persisting its state.



    I'm not sure how to fix this.

    Here is the runbook code:

    workflow SQLAzureReIndex 
    {
    
        param( 
            [parameter(Mandatory=$True)] 
            [string] $SqlServer, 
             
            [parameter(Mandatory=$True)] 
            [string] $Database, 
             
            [parameter(Mandatory=$True)] 
            [PSCredential] $SqlCredential 
        ) 
    
            # Setup credentials   
        $SqlUsername = $SqlCredential.UserName 
        $SqlPass = $SqlCredential.GetNetworkCredential().Password
    
        inlinescript
        {
    
            # Create connection to DB
            $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,1433;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=9999999;")
            $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Output $event.Message }; 
            $Conn.add_InfoMessage($handler);
            $Conn.Open();
    
            # Create command to Re-Index all tables in DB on $ServerName
            $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
            $DatabaseCommand.Connection = $Conn
            $DatabaseCommand.CommandTimeout = 10000
            $DatabaseCommand.CommandText = @"
            DECLARE @TableName varchar(255)
     
            DECLARE TableCursor CURSOR FOR
            (
            SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
            FROM INFORMATION_SCHEMA.TABLES IST
            WHERE IST.TABLE_TYPE = 'BASE TABLE'
            )
            
            OPEN TableCursor
            FETCH NEXT FROM TableCursor INTO @TableName
            WHILE @@FETCH_STATUS = 0
            
            BEGIN
            PRINT('Rebuilding Indexes on ' + @TableName)
            Begin Try
            EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
            End Try
            Begin Catch
            PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
            EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
            End Catch
            FETCH NEXT FROM TableCursor INTO @TableName
            END
            
            CLOSE TableCursor
            DEALLOCATE TableCursor
    "@
    
            $DatabaseCommand.ExecuteNonQuery()
    
    
            # Close connection to Master DB
            $Conn.Close() 
        }    
    }
    

    Tuesday, August 19, 2014 7:00 PM

Answers

  • Hi, 

    You are probably running into our fairshare feature. We suspend any runbook job that is taking longer than 30 minutes to complete in order to give other jobs a chance to run, and then resume the runbook job from its last checkpoint. If there are no checkpoints, it starts again from the beginning.

    See http://social.msdn.microsoft.com/Forums/windowsazure/en-US/0befc51a-6941-48c0-83b3-b0bdf15da200/runbook-restarted-while-executing-azure-automation?forum=azureautomation for details.

    My guess is that this runbook fails only with larger tables because larger tables are taking more than 30 minutes for this operation to complete, and the runbook is waiting around for the operation to complete. After 30 minutes, the runbook is removed so that other runbooks may run, and when it comes back it starts again from the beginning, causing the issue to happen again and again until our system gives you back that error you received.

    Could you instead perform the action asynchronously, and end the runbook after kicking it off?

    • Proposed as answer by Joe Levy_ Tuesday, August 19, 2014 7:51 PM
    • Marked as answer by ShowManzer Tuesday, August 26, 2014 2:41 AM
    Tuesday, August 19, 2014 7:51 PM
  • Perhaps something like below might do the trick.

    workflow Set-SQLIndex
    {
        param(
            [parameter(Mandatory=$True)]
            [string] $SqlServer,
        
            [parameter(Mandatory=$True)]
            [string] $Database         
        )
    
        $SqlUsername = "<yourusername>"
        $SqlPass = "<yourpassword>"
        $SqlServerPort = 1433
        
        
        $TableNames = Inlinescript {
            # Define the connection to the SQL Database
            $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
            
            # Open the SQL connection
            $Conn.Open()
    
            # Define the SQL command to run.
            $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES ", $Conn)
            $Cmd.CommandTimeout=120
    
            # Execute the SQL command
            $Ds=New-Object system.Data.DataSet
            $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
            [void]$Da.fill($Ds)
    
            # Output the tables
            $Ds.Tables.Table_Name
    
            # Close the SQL connection
            $Conn.Close()
        }
        
        ForEach ($TableName in $TableNames)
        {
          Write-Output "Creating checkpoint in case the runbook gets interrupted "
          Checkpoint-Workflow
          Write-Output "Indexing Table $TableName..."
          InlineScript {
              
            $SQLCommandString = @"
            Begin Try
            EXEC('ALTER INDEX ALL ON $Using:TableName REBUILD with (ONLINE=ON)')
            End Try
            Begin Catch
            EXEC('ALTER INDEX ALL ON $Using:TableName REBUILD')
            End Catch  
    "@
    
            # Define the connection to the SQL Database
            $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
            
            # Open the SQL connection
            $Conn.Open()
    
            # Define the SQL command to run.
            $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
            $Cmd.CommandTimeout=120
    
            # Execute the SQL command
            $Ds=New-Object system.Data.DataSet
            $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
            [void]$Da.fill($Ds)
    
            # Close the SQL connection
            $Conn.Close()
          }  
        }
        Write-Output "Finished Indexing"
    }


    This posting is provided AS IS with no warranties, and confers no rights.

    • Marked as answer by ShowManzer Tuesday, August 26, 2014 2:40 AM
    Tuesday, August 26, 2014 2:02 AM
    Owner

All replies

  • Hi, 

    You are probably running into our fairshare feature. We suspend any runbook job that is taking longer than 30 minutes to complete in order to give other jobs a chance to run, and then resume the runbook job from its last checkpoint. If there are no checkpoints, it starts again from the beginning.

    See http://social.msdn.microsoft.com/Forums/windowsazure/en-US/0befc51a-6941-48c0-83b3-b0bdf15da200/runbook-restarted-while-executing-azure-automation?forum=azureautomation for details.

    My guess is that this runbook fails only with larger tables because larger tables are taking more than 30 minutes for this operation to complete, and the runbook is waiting around for the operation to complete. After 30 minutes, the runbook is removed so that other runbooks may run, and when it comes back it starts again from the beginning, causing the issue to happen again and again until our system gives you back that error you received.

    Could you instead perform the action asynchronously, and end the runbook after kicking it off?

    • Proposed as answer by Joe Levy_ Tuesday, August 19, 2014 7:51 PM
    • Marked as answer by ShowManzer Tuesday, August 26, 2014 2:41 AM
    Tuesday, August 19, 2014 7:51 PM
  • Thanks for the response, Joe

    How would I perform is asynchronously? My SQL-fu is pretty poor :/

    Wednesday, August 20, 2014 6:08 PM
  • So is mine unfortunately :(

    Wednesday, August 20, 2014 11:17 PM
  • One option that might work for you is to not try and update all of the table indexes with a single SQL command call. You could make a call to retrieve all of the tables in the database and return this list to the workflow outside of the inlinescript.  You could then loop through the table names and call an inlinescript for each table and just index that one. You should then call Checkpoint-Workflow between each call to update a table so if your job is suspended and then resumed it will pick up from the last checkpoint and continue processing the remaining table names until it completes.

    If a single table takes longer than 30 minutes to index then this solution would not work and you would have to limit the type of indexing you do on that table to keep it under 30 minutes (perhaps getting the list of indexes on the table and then updating just that index and calling checkpoint-workflow between each index update on a table).

    Thanks,

    Eamon


    This posting is provided AS IS with no warranties, and confers no rights.

    Sunday, August 24, 2014 3:40 AM
    Owner
  • Thank you, Eamon. 

    That sounds like a great idea. Although, I'm not quite sure how to do that. 

    I'll do some research but any pointers would be greatly appreciated.

    Regards,

    Manzer

    Monday, August 25, 2014 8:45 PM
  • Perhaps something like below might do the trick.

    workflow Set-SQLIndex
    {
        param(
            [parameter(Mandatory=$True)]
            [string] $SqlServer,
        
            [parameter(Mandatory=$True)]
            [string] $Database         
        )
    
        $SqlUsername = "<yourusername>"
        $SqlPass = "<yourpassword>"
        $SqlServerPort = 1433
        
        
        $TableNames = Inlinescript {
            # Define the connection to the SQL Database
            $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
            
            # Open the SQL connection
            $Conn.Open()
    
            # Define the SQL command to run.
            $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT * FROM INFORMATION_SCHEMA.TABLES ", $Conn)
            $Cmd.CommandTimeout=120
    
            # Execute the SQL command
            $Ds=New-Object system.Data.DataSet
            $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
            [void]$Da.fill($Ds)
    
            # Output the tables
            $Ds.Tables.Table_Name
    
            # Close the SQL connection
            $Conn.Close()
        }
        
        ForEach ($TableName in $TableNames)
        {
          Write-Output "Creating checkpoint in case the runbook gets interrupted "
          Checkpoint-Workflow
          Write-Output "Indexing Table $TableName..."
          InlineScript {
              
            $SQLCommandString = @"
            Begin Try
            EXEC('ALTER INDEX ALL ON $Using:TableName REBUILD with (ONLINE=ON)')
            End Try
            Begin Catch
            EXEC('ALTER INDEX ALL ON $Using:TableName REBUILD')
            End Catch  
    "@
    
            # Define the connection to the SQL Database
            $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")
            
            # Open the SQL connection
            $Conn.Open()
    
            # Define the SQL command to run.
            $Cmd=new-object system.Data.SqlClient.SqlCommand($SQLCommandString, $Conn)
            $Cmd.CommandTimeout=120
    
            # Execute the SQL command
            $Ds=New-Object system.Data.DataSet
            $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
            [void]$Da.fill($Ds)
    
            # Close the SQL connection
            $Conn.Close()
          }  
        }
        Write-Output "Finished Indexing"
    }


    This posting is provided AS IS with no warranties, and confers no rights.

    • Marked as answer by ShowManzer Tuesday, August 26, 2014 2:40 AM
    Tuesday, August 26, 2014 2:02 AM
    Owner
  • Oh wow, you rock!

    I made some minor modifications to work with our system but it's running very well.

    Thanks a bunch!

    Tuesday, August 26, 2014 2:40 AM
  • I'm getting a different problem now:

    exception

    Maximum stream length exceeded (5000000 bytes)

    I've searched on the forums and Google and can't seem to find where this is coming from.

    Wednesday, August 27, 2014 6:20 PM
  • You will get this error if there is too much data in memory when you call Checkpoint-Workflow. Can you look at your runbook and see if there is potentially a lot of data in variables that you don't need later? You could call Clear-Item or Remove-Item on this data (or maybe setting it to $null) in order to remove it from the memory before the next Checkpoint-Workflow is called.

    Thanks,

    Eamon


    This posting is provided AS IS with no warranties, and confers no rights.

    Wednesday, August 27, 2014 6:53 PM
    Owner