Maximum Error Count Reached

Question

I created a maintenance plan to backup all the databases locally on a folder in E Drive.

Out of nowhere, the job is failing occasionally. sometimes the job runs successfully and sometimes sends an error.

The point of confusion is that even though the we receive an error that the job failed, i can see the latest .bak files in the directory. I even restored those db's and the data within the tables are synced. This happens in 7/10 errors. sometimes, there is only backup of master, model and user db and sometimes we have all the databases backed up even on failure.

Below is the error:

Message

Executed as user: WIN-N81LQ9R0SAA\localadmin . Microsoft (R) SQL Server Execute Package Utility Version 13.0.5830.85 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 3:53:00 PM Progress: 2020-11-18 15:53:01.44 Source: {F019CE0D-91E8-4CA8-9CCD-F5B3BB69E1D4} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2020-11-18 15:53:01.77 Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'E:\DatabaseB...".: 100% complete End Progress Progress: 2020-11-18 15:53:01.91 Source: Back Up Database Task Executing query "BACKUP DATABASE [master] TO DISK = N'E:\DatabaseB...".: 50% complete End Progress Progress: 2020-11-18 15:53:01.97 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2020-11-18 15:53:02.08 Source: Back Up Database Task Executing query "BACKUP DATABASE [model] TO DISK = N'E:\DatabaseBa...".: 50% complete End Progress Progress: 2020-11-18 15:53:02.13 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2020-11-18 15:53:02.37 Source: Back Up Database Task Executing query "BACKUP DATABASE [msdb] TO DISK = N'E:\DatabaseBac...".: 50% complete End Progress Progress: 2020-11-18 15:53:02.56 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2020-11-18 15:53:02.76 Source: Back Up Database Task Executing query "BACKUP DATABASE [xyz] TO DISK...".: 50% complete End Progress Progress: 2020-11-18 15:53:02.89 Source: Back Up Database Task Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2020-11-18 15:53:14.53 Source: Back Up Database Task Executing query "BACKUP DATABASE [abc] TO DISK = N'E:\D...".: 50% complete End Progress Error: 2020-11-18 15:53:17.54 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "declare @backupSetId as int select @backupSetId =..." failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The network connection was aborted by the local system.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2020-11-18 15:53:17.54 Code: 0x80019002 Source: Subplan_1 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:53:00 PM Finished: 3:53:17 PM Elapsed: 16.875 seconds. The package execution failed. The step failed.

Can someone please explain this strange behavior and help us resolving this issue?

Below is the error i got in Maintenace plan history. Error Number: -1073548784

Error Message: Executing the query "declare @backupSetId as int select @backupSetId =..." failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The network connection was aborted by the local system.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

TSQL Code: BACKUP DATABASE [master] TO DISK = N''E:\DatabaseBackups\Daily16\master_backup_2020_11_24_155301_7291762.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_2020_11_24_155301_7291762'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''master'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''master'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''master'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM DISK = N''E:\DatabaseBackups\Daily16\master_backup_2020_11_24_155301_7291762.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO BACKUP DATABASE [model] TO DISK = N''E:\DatabaseBackups\Daily16\model_backup_2020_11_24_155301_7331781.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_2020_11_24_155301_7331781'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''model'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''model'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''model'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM DISK = N''E:\DatabaseBackups\Daily16\model_backup_2020_11_24_155301_7331781.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO BACKUP DATABASE [msdb] TO DISK = N''E:\DatabaseBackups\Daily16\msdb_backup_2020_11_24_155301_7361812.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_2020_11_24_155301_7361812'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''msdb'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''msdb'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''msdb'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM DISK = N''E:\DatabaseBackups\Daily16\msdb_backup_2020_11_24_155301_7361812.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO BACKUP DATABASE [abc] TO DISK = N''E:\DatabaseBackups\Daily16\abc_backup_2020_11_24_155301_7391866.bak'' WITH NOFORMAT, NOINIT, NAME = N''abc_backup_2020_11_24_155301_7391866'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''abc'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''abc'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''abc'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM DISK = N''E:\DatabaseBackups\Daily16\abc_backup_2020_11_24_155301_7391866.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO BACKUP DATABASE [xyz] TO DISK = N''E:\DatabaseBackups\Daily16\xyz_backup_2020_11_24_155301_7411886.bak'' WITH NOFORMAT, NOINIT, NAME = N''xyz_backup_2020_11_24_155301_7411886'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N''xyz'' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N''xyz'' ) if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''xyz'''' not found.'', 16, 1) end RESTORE VERIFYONLY FROM DISK = N''E:\DatabaseBackups\Daily16\xyz_backup_2020_11_24_155301_7411886.bak'' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND GO

Answer 1

The error looks very clear:

"A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The network connection was aborted by the local system.)".

So the problem was the connection.

The error goes on and gives us a few hints (Possible failure reasons):

1. Problems with the query: I don't think so, the query is always the same
2. "ResultSet" property not set correctly: I don't think so, the query just fails occasionally
3. parameters not set correctly: I don't think so, the query is always the same and just fails occasionally
4. or connection not established correctly: well, this looks the case to me as "A transport-level error has occurred "

Then the error goes on and suggests us what to do in order to avoid such scenario in the future:

The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

So if you will change the MaximumErrorCount to 3 you will actually put in place a retry logic and SQL Server will try to execute your query multiple time and as result you will reduce the chance of failure.

BONUS: Investigate your network and find out why you are experiencing disconnections

EDIT after error and T-SQL update: Your maintenance plan is probably structured in 2 phases:

1. Backup the database
2. Copy the database somewhere on the network

That's why there is no problem with the database backup. The backup arrives to 100% and you are always able to restore it. Then the file is copied somewhere in the network and 7/10 there is a network glitch. I found the solution here.

Other alternatives solutions: Backups failing for all databases

Answer 2

It is close to impossible to get any useful information from the Job step output for maint plans, as you have noticed.

Have the maint plan create a report file and check that report file for error messages. Also check the main plan history table for errors.