I have a SQL Server 2008 R2 instance that I've been managing for many years.
I have a daily backup maintenance plan that has 4 steps:
- Backup the databases (3 databases)
- Backup the transaction logs (3 databases)
- Delete backup files older than n days
- Delete transaction logs older than n days
A few days ago the backup (step#1) failed due to disk full. I fixed that by deleting some backups, and reducing the number of days of backups to keep.
But ever since then the maintenance plan keeps failing. It does steps 1 & 2 successfully, but doesn't go on to do steps 3 and 4. And I can't find any error messages explaining why.
The Windows application reports that the maintenance plan failed, but no reason is given:
SQL Server Scheduled Job 'DailyFullBackupAndCleanup.Subplan_1' (0xEC4CDE928E25FE4B964CE0758108D70D) -
Status: Failed - Invoked on: 2017-04-18 02:02:00 -
Message: The job failed.
The Job was invoked by Schedule 21 (DailyFullBackupAndCleanup.Subplan_1).
The last step to run was step 1 (Subplan_1).
The text file output of steps 1 & 2 all indicate the backups completed successfully, no errors, and the backups are there. The SQL Agent History indicates that
Message
Executed as user: NETLINE\NLSQL06$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 2:02:00 AM Progress: 2017-04-18 02:02:00.84 Source: {5AE5C47F-9694-406C-8F29-A779A2B132C8}
Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2017-04-18 02:02:01.07 Source: Back Up Database (Full)
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\MSSQL_Bac...".: 33% complete End Progress Progress: 2017-04-18 02:02:01.07 Source: Back Up Database (Full)
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\MSSQL_Bac...".: 66% complete End Progress Progress: 2017-04-18 02:02:01.07 Source: Back Up Database (Full)
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\MSSQL_Bac...".: 100% complete End Progress Progress: 2017-04-18 02:02:01.60 Source: Back Up Database (Full)
Executing query "BACKUP DATABASE [aspnetdb] TO DISK = N'E:\MSSQL_B...".: 50% complete End Progress Progress: 2017-04-18 02:02:01.65 Source: Back Up Database (Full)
Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2017-04-18 02:25:09.87 Source: Back Up Database (Full)
Executing query "BACKUP DATABASE [NLProduction] TO DISK = N'E:\MSS...".: 50% complete End Progress Progress: 2017-04-18 02:29:33.18 Source: Back Up Database (Full)
Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2017-04-18 02:29:34.85 Source: Back Up Database (Full)
Executing query "BACKUP DATABASE [ReportServer] TO DISK = N'E:\MSS...".: 50% complete End Progress Progress: 2017-04-18 02:29:35.16 Source: Back Up Database (Full)
Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2017-04-18 02:29:35.23 Source: Back Up Database (Transaction Log)
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\MSSQL_Bac...".: 33% complete End Progress Progress: 2017-04-18 02:29:35.23 Source: Back Up Database (Transaction Log)
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\MSSQL_Bac...".: 66% complete End Progress Progress: 2017-04-18 02:29:35.23 Source: Back Up Database (Transaction Log)
Executing query "EXECUTE master.dbo.xp_create_subdir N'E:\MSSQL_Bac...".: 100% complete End Progress Progress: 2017-04-18 02:29:35.35 Source: Back Up Database (Transaction Log)
Executing query "BACKUP LOG [aspnetdb] TO DISK = N'E:\MSSQL_Backup...".: 50% complete End Progress Progress: 2017-04-18 02:29:35.54 Source: Back Up Database (Transaction Log)
Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2017-04-18 02:29:38.90 Source: Back Up Database (Transaction Log)
Executing query "BACKUP LOG [NLProduction] TO DISK = N'E:\MSSQL_Ba...".: 50% complete End Progress Progress: 2017-04-18 02:29:39.92 Source: Back Up Database (Transaction Log)
Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress Progress: 2017-04-18 02:29:40.00 Source: Back Up Database (Transaction Log)
Executing query "BACKUP LOG [ReportServer] TO DISK = N'E:\MSSQL_Ba...".: 50% complete End Progress Progress: 2017-04-18 02:29:40.06 Source: Back Up Database (Transaction Log)
Executing query "declare @backupSetId as int select @backupSetId =...".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:02:00 AM Finished: 2:29:40 AM Elapsed: 1659.84 seconds. The package execution failed. The step failed.
Can anyone give me any clues on how to find the error message? Thanks.
