Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2002-06-03 : 12:37:10
|
| Hi, I have a db maintenance plan which backs up my test dbs nightly. Because I have a space problem and I backup daily, I want to delete the old backup files of test dbs everyday before it creates a new backup file.For some reason, even though I checked 'Remove files older than 1 (day)', my old backup files do not get deleted! This causes next day's backup to fail because now there is no space (due to 2 or 3 days files sitting there)!Can it be a security issue? As a DBA, I do have read/write access to backup folder so I can manually do anything but I don't think my security profile matters as this plan runs as an 'sa' so why does it not delete historical files?I have recreated the plan 2 times and I made sure it is exactly like my other plans for prod n dev dbs all running sucessfully!What am I missing here?Thanks,Sarat. |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-06-03 : 16:03:07
|
| I hate to pupu(sic) the maintenance plan, but If I needed that much control over a backup I would have it done with SQLAgent and a good 'ole WITH INIT at the end:Ex:Backup Transaction DBName with Truncate_onlygoBackup Database DBName to disk = 'd:\Backups\DBName_Data.bak'with INIT, STATS = 10HTH,JamesH. |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-06-03 : 16:03:18
|
| First of all, the maintenance plans don't delete old files until after the backup procedure completes. So you'll need room for at least two copies. Second, if you set the maintenance plans to delete files older than one day, it deletes files older than 24 hours exactly. So if your backup runs a couple seconds faster the second night the first one will not be more than 24 hours old and it will stay. Just some querky stuff you have to deal with when using maintenance plans. I had this problem on a server once and created a work around until we were able to purchase more disk space. After creating the maintanence plan I edited the job the plan created and added a step, before the backup executed, that deleted all backup files. This is a little risky though. If you delete you disk backup and then your current backup fails and you have a problem, you better have reliable tape backups on hand. If you need finer control than this you will need to write your own custom backup scripts.Jeff BanschbachConsultant, MCDBA |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2002-06-03 : 17:56:36
|
| Jeff, Thanks so much for the information. Your point is right (backup may run first and then delete) but I am surprised that dbs like master, model and msdb have backup files from last 3 days. I have one plan for 5 dbs which runs every night, 3 of which mentioned above backup files are not getting deleted. I have repeatedly checked that I have checked the box for 'Remove files older than 1 day(s).'Can this happen - The plan first tries to backup every db and if it fails - it never gets to the step to delete old copies of any dbs.I am going to try 2 separate plans now and see what happens. Sarat. |
|
|
|
davy_boy2000
Starting Member
28 Posts |
Posted - 2002-06-04 : 07:02:44
|
| It definately has to backup before delete. I had this problem when I just selected all 'user' databases, as there wasn't enough disk space for two copies of the databases. I did fix the delete if older than one day, by changing to if older than 23 hours. The space problem I had to fix with more hardware, deleting before backup is scary...!! |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-06-04 : 15:03:53
|
| Sarat, Yes that can happen. The plan will perform all backup operations before delete operations. So, if you are trying to backup multiple databases on the same plan, and the plan fails during one of the backups no files will be deleted.Jeff BanschbachConsultant, MCDBA |
|
|
|
|
|
|
|
|
Backup Job Problem (Remove folder Older than..)