| Author |
Topic |
|
ssprague
Starting Member
4 Posts |
 Posted - 2010-12-20 : 11:57:43
|
| I am very new to the sql server world. I have sql server 2005. Through the maintenance wizard I have a maintenance plan that does a full backup everynight and transaction log backups are done every 2 hours. Also in the plan, I delete the transaction log backups if they are older than 24 hrs. However, the delete is not working. I was thinking that after a full back up that I would not need the transaction logs that were older than the full backup. Please let me know where my thinking has gone wrong. Thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ssprague
Starting Member
4 Posts |
Posted - 2010-12-20 : 15:10:02
|
| I believe I have 1 full day of trans logs plus the current day being processed. The delete process is suppose to run at 2:30 a.m. and the trans logs start at 3:00 a.m. In a 3 day scenario, the 1st days logs would not be deleted until 2:30 a.m. on the 3rd day. My problem is the logs are not deleting. Any suggestions? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-12-20 : 16:24:30
|
| Verify that you have the right path and extension for the transaction log backups in the maintenance cleanup task. If you have .trn - then it will not work. That is probably the most common issue people have with this task.Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ssprague
Starting Member
4 Posts |
Posted - 2010-12-20 : 16:35:58
|
| I recreated the maintenance plan and did a test run. It seems to be working now. Will know for sure tonight when it runs. Not sure what I did differently but like you said, they are buggy. Thank you for your help! |
|
|
|
ssprague
Starting Member
4 Posts |
Posted - 2010-12-20 : 16:37:17
|
| I think jeffw might be referring to the . in front of the trn. Just trn seems to work. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-12-22 : 21:16:11
|
| Correct - .trn does not work in the maintenance cleanup task. This can easily be verified by generating the code that will be used for that task. It should be something like:Execute master.dbo.xp_delete_file 0,'{path}','bak','{datetime}'If you see this:Execute master.dbo.xp_delete_file 0,'{path}','.bak','{datetime}'It won't work - because the way the procedure has been written it ends up trying to remove files as: {path}\..bak - and since your backup files do not have two '.' in the name they are not found and are not deleted.JeffJeff |
|
|
|
|
trans log backup