Maximum Error Count Reach ( SQL 2016)

Question

I hope someone from the SQL DBA community can answer my question. although I tried to search on the issues here, but couldn't find a exact response so create a new question.

I created maintenance plan to backup all the databases locally on a folder in E Drive. Out of no where, the job is failing occasionally. sometime the job run successfully and sometime 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 @George Wang 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?

Regards,

Azeem

Answer 1

Good day,

It could help if you provided the log information in a readable way with line breaks.

Log information

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.  

The error

According to the error log information the actual error is this: A transport-level error has occurred when receiving results from the server.

Possible reasons and solutions

That's a connectivity error. Once the job encounter such issue, it simply stop.

Transport level errors are often linked to the connection to sql server being broken from the server side.

For example, the database connection is closed by the database server. Can be a result of "SQL Server" service stopped, or a configuration of the process which include SET SINGLE_USER which close all connections. Another option is if the database AUTO_ CLOSE option is set to ON.

In such cases, the connection remains valid in the connection pool of your app and as a result, when you pickup the shared connection string and try to execute next step then it's not able to reach the database.

When AUTO_CLOSE is enabled, then the database shuts down and its resources are freed after the last user disconnected from the database. AUTO_CLOSE spins the database up automatically when a connection is made, but this can take time and the error might raise. In most cases AUTO_CLOSE should NOT be enabled!

(1) Check the configuration of the AUTO_CLOSE of all the databases
(2) Monitor server restart.
(3) Monitor database close (using Extended Events you can monitor Database Events)

Answer 2

Hi @azeem khan ,

@Ronen Ariely 's reply is very helpful.

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.)

Quote code from this case: error-a-transportlevel-error-has-occurred-when-sending-the-request-to-the-server-provider-tcp

ALTER DATABASE YourDatabaseName SET AUTO_CLOSE OFF;   

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.

Reference [run-sql-server-agent-job-from-ssis] to create sql agent job from SSIS, 2, if you want to backup mutiple dbs automatically, you can also follow steps as this:
how-to-backup-multiple-sql-server-databases-automatically

More information:how-to-delete-old-database-backup-files-automatically-in-sql-server, ssis-package-does-not-run-when-called-from-a-sql-server-agent-job-step

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi @azeem khan ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.