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

I hope someone from the database administrators 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.

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

Regards,

Azeem

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.

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

I hope someone from the database administrators 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.

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.

I hope someone from the database administrators 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.

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.