using the alerts application to report maintenance failures
Good morning, I created a runbook to schedule and run index maintenance on our production dbs. A snippet of that runbook follows: Checkpoint-Workflow 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. In this case we are getting the number of rows in the table $Cmd=new-object system.Data.SqlClient.SqlCommand("alter index IX_Clicks_SendId on dbo.Clicks REBUILD with (ONLINE=ON)", $Conn) $Cmd.CommandTimeout=72000 # 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() filter timestamp {"$(Get-Date -Format G 😞 $_"} Write-Output ("Dbo.Clicks IX_Clicks_SendId index maintenance finished") | timestamp } Checkpoint-Workflow 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. In this case we are getting the number of rows in the table $Cmd=new-object system.Data.SqlClient.SqlCommand("alter index all on dbo.EmailAddressSummaries REBUILD with (ONLINE=ON)", $Conn) $Cmd.CommandTimeout=72000 # 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() filter timestamp {"$(Get-Date -Format G 😞 $_"} Write-Output ("Dbo.EmailAddressSummaries index maintenance finished") | timestamp } The output follows. The job is marked as 'completed' as one index is successfully rebuilt and one errors out due to a lock conflict. I wish to use the alerts system flag this error and send an email. I have been able with my IT teams help to create an alert rule and connect to an action group to flag a job marked as 'failed' and send an email. That is not what is happening here. The job is marked as 'completed' with an error condition within the job. Can I design an alert rule/action group to flag this kind of error within a completed job and send an email when this occurs? Thank you, (sorry for the verboseness of this message - I don't know how to explain it in a shortened form). 8/6/2020, 5:32:23 AM Output 8/6/2020 9:32:23 AM: Dbo.Clicks IX_Clicks_SendId index maintenance finished 8/6/2020, 5:45:22 AM Error System.Management.Automation.MethodInvocationException: Exception calling "Fill" with "1" argument(s): "Could not proceed with index DDL operation on table 'EmailAddressSummaries' because it conflicts with another concurrent operation that is already in progress on the object. The concurrent operation could be an online index operation on the same object or another concurrent operation that moves index pages like DBCC SHRINKFILE." ---> System.Data.SqlClient.SqlException: Could not proceed with index DDL operation on table 'EmailAddressSummaries' because it conflicts with another concurrent operation that is already in progress on the object. The concurrent operation could be an online index operation on the same object or another concurrent operation that moves index pages like DBCC SHRINKFILE. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at CallSite.Target(Closure , CallSite , Object , Object ) --- End of inner exception stack trace --- at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo) at CallSite.Target(Closure , CallSite , Object , Object ) at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1) at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame) at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) 8/6/2020, 5:45:22 AM Output 8/6/2020 9:45:22 AM: Dbo.EmailAddressSummaries index maintenance finished