0

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

3

2 Answers 2

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

9
  • Hi Francesco, SQL server and the backup repository is on the same server. sometime even if the there is error from the job, i can see the databases being backed up. i then tried and create a maintenance plan on another sql14 instance on the same server. The backup run flawlessly without any issue.
    – Azeem Khan
    Nov 26, 2020 at 7:19
  • also, you mentioned to investigate the network... SQL management studio, SQL server instance, Backup volume are all on the same server, how can this be a network issue and if it is can you please suggest some point on how i should check any network issue.
    – Azeem Khan
    Nov 26, 2020 at 7:22
  • Is this the only backup you have in place or are there other third party backups? How many databases are you backing up? Nov 26, 2020 at 7:44
  • Your backup might be local but that drive is maybe shared on the network. Can you paste here the T-SQL code of your backup? Or maybe setup Ola's backup: ola.hallengren.com Nov 26, 2020 at 9:52
  • You can avoid the network issues this way: support.solarwinds.com/SuccessCenter/s/article/… Nov 26, 2020 at 9:59
-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.