October 1, 2010 at 12:39 am
Hi,
I have a maintenance plain with two task that delete '.bak' and 'trn.' files older than 1 week from the Backup Disk. This task are SQL Server 'Maintenance clean up Task' type. My SQL Server is SQL Server 2008 x64 Standard Edition and is in Cluster.
Randomly the job that run this maintenance plan is failing. If i see in the job history i can see the next error message:
"...
Source: Cleanup BAK files Executing query "EXECUTE master.dbo.xp_delete_file 0,N'E:\SQLBackup...".: 100% complete End Progress Error: 2010-10-01 04:15:02.29 Code: 0xC0024104 Source: Reporting Task for subplan-{4012D2E7-619C-4463-A651-3D7A7B9226CD} Description: The Execute method on the task returned error code 0x80131500 (Failed to retrieve data for this request.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2010-10-01 04:15:02.29 Code: 0x80019002 Source: GlobalMaintenancePlan Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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: 4:15:00 Finished: 4:15:02 Elapsed: 2.06 seconds. The package execution failed. The step failed.
...
"
But if i see in the maintenance plain report file i can see that '.bak' and '.trn' task have been runned succesfully. The log file content is:
"
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.2531
Report was generated on "SQLServer".
Maintenance Plan: GlobalMaintenancePlan
Duration: 00:00:01
Status: Succeeded.
Details:
Cleanup TRN files (SQLServer)
Task start: 2010-10-01T04:15:00.
Task end: 2010-10-01T04:15:01.
Success
Cleanup BAK files (SQLServer)
Task start: 2010-10-01T04:15:01.
Task end: 2010-10-01T04:15:02.
Success
"
Could someone tell me why this happens? How could I fix these errors?
Many thanks in advance.
October 1, 2010 at 1:51 pm
cant really see why it is blowing up but here is a work around...create a back job(s) to run using the following
--db example
BACKUP DATABASE [te_content] TO DISK = N'D:\DB_Files\Backups\te_content_backup.bak'
WITH RETAINDAYS = 7, FORMAT, NO INIT,NAME = N'te_content_backup_FULL',
SKIP, REWIND, NOUNLOAD, STATS = 10
--log example
BACKUP LOG te_content TO DISK = N'D:\DB_Files\BACKUPS\Database_Log.trn' WITH RETAINDAYS = 7, NOFORMAT, NOINIT,
NAME = N'[tDatabase_TransactionLogBackup]', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
this will keep your log and or DB for days 7 days...and will append each time you back it,then delete after its 7 days old
October 4, 2010 at 7:41 am
Thanks for your response SSC, but unfornately it is not valid for me.
For example each time i add a new database to my SQL Server instance i have to update the scripts that you said me to include it in the backups. Maintenance plain maintenance is easier using SQL Server 'Maintenance clean up Task' objects. But the principal inconvenient is than in my maintenance plain after i do a full or transaction backup inmediately i move the backup file to another location, so i can´t use BACKUP sentence with "WITH RETAINDAYS".
Really i don´t know why randomly the job that run this maintenance plan (with contain only SQL Server 'Maintenance clean up Task' object) is failing.
Any other idea?
Many thanks in advance.
October 4, 2010 at 9:31 am
in that case follow this http://www.mssqltips.com/tip.asp?tip=1070
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
October 5, 2010 at 3:53 am
Hi SSC,
It is very interesting script to backup databases but this action already is doing correctly with my SQL Server maintence plain who is using SQL Server 'Back up Database Task' object. Concretly this maintenance plain is doing three steps:
Step1 --> Backup user databases.
Step2 --> Compressed the backup files obtained in the Step1 and move to another remote backup location.
Step3 --> Move the backup files obtained in the Step1 to a local disk historical folder.
But at the same time i have other maintenance plain with two task that delete '.bak' (and 'trn)' files from the mentioned historical folder that are older than 1 week. This task are SQL Server 'Maintenance clean up Task' type and randomly the job that run this maintenance plan is failing. If i see in the job history i can see the next error message:
"...
Source: Cleanup BAK files Executing query "EXECUTE master.dbo.xp_delete_file 0,N'E:\SQLBackup...".: 100% complete End Progress Error: 2010-10-01 04:15:02.29 Code: 0xC0024104 Source: Reporting Task for subplan-{4012D2E7-619C-4463-A651-3D7A7B9226CD} Description: The Execute method on the task returned error code 0x80131500 (Failed to retrieve data for this request.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2010-10-01 04:15:02.29 Code: 0x80019002 Source: GlobalMaintenancePlan Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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: 4:15:00 Finished: 4:15:02 Elapsed: 2.06 seconds. The package execution failed. The step failed.
...
"
But if i see in the maintenance plain report file i can see that '.bak' and '.trn' task have been runned succesfully. The log file content is:
"
Microsoft(R) Server Maintenance Utility (Unicode) Version 10.0.2531
Report was generated on "SQLServer".
Maintenance Plan: GlobalMaintenancePlan
Duration: 00:00:01
Status: Succeeded.
Details:
Cleanup TRN files (SQLServer)
Task start: 2010-10-01T04:15:00.
Task end: 2010-10-01T04:15:01.
Success
Cleanup BAK files (SQLServer)
Task start: 2010-10-01T04:15:01.
Task end: 2010-10-01T04:15:02.
Success
"
I dont understand why this happens. Any idea to fix these errors?
Many thanks in advance.
October 5, 2010 at 7:20 am
This is not very easy to track down, since the maintenance task creates an SSIS packasge in the background. You will see that in the error.
My best suggestion is to use the script, and not waste hours trying to figure out why the package is failing. If must know that then you need to find the package and open it in SSIS and then try to track the error down.
At the end of the T-SQL is probably going to be your best workaround.....stick it in a job and be done with it!
good luck
October 5, 2010 at 7:28 am
Ok,
Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply