April 27, 2009 at 9:11 am
Hi:
Why would all the Maintenance plan work and without giving any error the step for deleting the old transaction logs (older that the las full backup) don't work, it never happens. I just don't get it.
Any help will be appreciated.
FJM
April 27, 2009 at 10:08 am
How is the task setup? What is the value you put for the file extension? Are the backups in separate folders for each database? If so, did you check the box to cleanup sub directories?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2009 at 12:22 pm
The Task Setup is:
1) First I chek the DB integrity,
2) then Update Statistics,
3) Full Backup of the DBs,
4) Cleanup of the past backup files
5) Transaction log Backup(so the trn log dont continue to grow),
***6) Cleanup of the past Transaction log backup files (Ive tried with 0, 1 and 2 days on the "Delete files older than the following" parameter but it doesn't do anything)
7) Reorginize Index
I got to be honest, I have no idea where the box for cleanup sub directories is.
April 27, 2009 at 12:36 pm
What is your SQL Server version?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 27, 2009 at 12:51 pm
Okay, there are a couple of issues with your plan - but I'll get to that later. The Maintenance Plan cleanup task has several options that need to be set.
Delete files of the following type: Backup Files should be selected
Search folder and delete files based on extension should be checked.
----- make sure the folder is correct
----- make sure the extension is correct (without the dot)
If your backups are going to separate folders - check the box to Include first-level sub-folders (will not be available if the client tools or SQL Server is not on SP2 or greater).
Then, you have to check the box to delete files based on the age and the appropriate selections.
Now, on to your maintenance plan itself.
You have a transaction log backup scheduled in the same plan as your daily maintenance. That tells me you don't have another plan to back it up on a regular basis (I recommend at least hourly). I would remove that task and create a new sub-plan/maintenance plan just for backing up the transaction log.
I would keep the step for removing the old backup files for both the full and transaction logs in the daily plan (we want to keep them all together), and I would set them up to delete anything older than 23 hours (full backups), and 25 hours (transaction logs). That way, I know I will always have a full days worth of backups online to restore from. If I have the storage available - I would increase that as much as possible.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2009 at 11:47 am
PaulB (4/27/2009)
What is your SQL Server version?
That's what I was wondering. Wasn't there a bug in the original version that was fixed by a leter Service pack ?
April 28, 2009 at 1:24 pm
My version info is:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
April 29, 2009 at 8:26 am
I know this wasn't specifically asked about but I thought I'd add a little FYI anyway. SQL 2005 SP2 does has a bug around clean-up of the maintenance plan report files (txt). Specifically, it won't auto-delete / clean them based on the age of the file. This is fixed in SP2 Update 9 (I think it's update 9) - of course, I'd recommend just applying SP3 at this point.
April 29, 2009 at 8:33 am
Yes, when I read your replay I realized that that might be the issue. Didn't the SP3 had some issues of slowing down the server or something? I kind of remember reading something about that , I'm not sure if it was for SQL SERVER 2005 SP3 but do you know?
May 15, 2009 at 12:42 pm
Jeffrey Williams (4/27/2009)
Okay, there are a couple of issues with your plan - but I'll get to that later. The Maintenance Plan cleanup task has several options that need to be set.Delete files of the following type: Backup Files should be selected
Search folder and delete files based on extension should be checked.
----- make sure the folder is correct
----- make sure the extension is correct (without the dot)
Aha ! That was my problem !
November 9, 2009 at 11:34 am
You wrote: "... I would set them up to delete anything older than 23 hours (full backups), and 25 hours (transaction logs)..."
How do you setup to delete .BAK file in SQL2005 that are older than 23 hours? I thought it's only by days if setting it up in the maintenance plan. Do you use a script for this (using xp_commandshell, vbs or something). Thanks for your assistance in advance.
- SQLNewbie
November 9, 2009 at 11:58 am
SQLNewbie-610691 (11/9/2009)
You wrote: "... I would set them up to delete anything older than 23 hours (full backups), and 25 hours (transaction logs)..."How do you setup to delete .BAK file in SQL2005 that are older than 23 hours? I thought it's only by days if setting it up in the maintenance plan. Do you use a script for this (using xp_commandshell, vbs or something). Thanks for your assistance in advance.
- SQLNewbie
If you use the standard maintenance cleanup task - you have the options for hours, days, weeks and months (i think).
I have modified my approach and now use an Execute SQL Task where I calculate the datetime to remove files by inspecting the history tables. I find a list of full backups and grab the version I want (0 is current backup, 1 would be previous, etc...). Then, using that datetime I call the undocumented procedure xp_delete_files with the appropriate properties to delete all bak and trn files before that datetime.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 9, 2009 at 12:00 pm
In the Maintenance Cleanup Task theirs an option under File Age, it’s a checkmark that reads: "Delete files based on the age of the file at task run" and under it theirs the option to choose between hours, days, weeks, month and years.
If you can’t see this, verify the version of your SQLS2k5. I had to upgrade to SP3 to be able to do it.
Hope it helps.
FJM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply