July 12, 2005 at 11:38 am
Hi there,
I have a few sites now that I've created maint jobs for and have set them up to delete the files older than 2wks for both transaction and database. Well, the jobs have been working fine, to the point that over the last 2months (these are remote sites that I dont frequent too often) there are dozens of backups and transaction logs.
So did I do something wrong? I've checked the system logs and I cannot see any error messages.
regards,
Chris
July 12, 2005 at 1:42 pm
Are you using SQL server or MSDE
July 12, 2005 at 1:54 pm
(sorry I always leave off the specifics)
MSSQL 2000, sp3a
Basically the maint job was created via. wizard for creating maint jobs.
July 13, 2005 at 8:16 am
Do you have all databases selected for backup? If so, and any of those databases are in simple recovery mode the transaction log backup will fail, which causes the backup job to fail and the delete of the old backups to never occur.
July 13, 2005 at 11:02 am
hi Folks
Had the same problem and and it was because on of the jobs is failing right click on the maintenance plan; clikc on history and check the success of the databases... Deletion of the files is done after all databases have been successfully deleted. You can use this SP if you cant get it working...
CREATE PROCEDURE sp_delete_backup_scripts
as
DECLARE @DatabaseName varchar(255)
declare @hr varchar(10), @ole_FileSystem int , @dbpath varchar(255)
DECLARE ListDatabasesBackedUp CURSOR FOR
select database_name,
physical_device_name
from msdb.dbo.backupset as bkupset,
msdb.dbo.backupmediafamily as bkupfamily
where type='D'
and backup_start_date >
(dateadd(hh,-25,getdate()))
and bkupfamily.media_set_id =
bkupset.media_set_id
OPEN ListDatabasesBackedUp
FETCH NEXT FROM ListDatabasesBackedUp INTO @DatabaseName , @dbpath
WHILE @@FETCH_STATUS = 0
BEGIN
--print @DatabaseName
--print @dbpath
EXEC @hr = master.dbo.sp_OACreate 'Scripting.FileSystemObject',@ole_FileSystem OUT
print @ole_FileSystem
EXEC @hr = master.dbo.sp_OAMethod @ole_FileSystem, 'DeleteFile', NULL,@dbpath
print @ole_FileSystem
Print @dbpath +'........................Deleting'
EXEC @hr = master.dbo.sp_OADestroy @ole_FileSystem
Print @dbpath +'........................Deleted'
FETCH NEXT FROM ListDatabasesBackedUp INTO @DatabaseName, @dbpath
END
CLOSE ListDatabasesBackedUp
DEALLOCATE ListDatabasesBackedUp
GO
Regards,
M
July 13, 2005 at 11:34 am
Do you backup to tape or disk?
If to disk, does your backup job create new files each time or just append to the original?
The removal of old backup files (using EXPIREDATE or RETAINDAYS) was really intended for tape backups. However, it will work on disk backups as long as you are creating NEW backup files each time you do a backup. If you are just appending to an existing backup, then the date of the file is the date of the last append, and the file will NEVER expire.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply