Maintenance cleanup task not functioning as planned

Question

Maintenance cleanup task not functioning as planned

Lowry Kozlowski

SSCommitted

Points: 1918
More actions
January 12, 2007 at 1:00 pm

#171779

I am working on SQL2005 x64 SP2 on Windows 2003 x64 sp1. I have successfully created my maintenace plans for backing up the databases and transaction logs. I am having all kinds of problems with the CLEANUP Task and cannot figure out why I am having such buggy problems. I need help.
My translog backups are running every hour with a full backup nightly, and reports are written and stored to a special reports folder on the same disk as our current backups. My plan is to keep only one day of transaction logs (24 at once) for each database and the same for the report which is txt. I set this all up using the Maintenace plan task and used 2 maintenance cleanup tasks, one for cleaning up the trn file and one for cleaning up the Maintenace plan report files. I sent the backup files to point to the location of the transaction logs with the file extension of "trn" (default value without the quotes) and set it to keep only one days data. After it failing many times I finally got it to clean up the data but it wiped out all but one log. Not exactly what I want. Work around is currently 2 days.
I did the same for the reports file again taking the defaults and scheduling 1 day data to retrieve. It points to the correct file, the files have the txt extension and nothing gets cleaned up.
These two routines are part of the same maintenance plan with the tranlog cleanup running first and then points to the report clean up. I don't have many options to mess up on here but still cannot figure out why I cannot get this to work
January 15, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 1

I wish I had some answers and I am experiencing these same type issues. For the life of me I don't know why such a simple thing is so difficult.

Are you running SP2 for SQL Server 2005 ??? If so, I am running Sp1 with the hotfix on top of that, not Sp2.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

I ran into the same problem, and I wish I had a good solution. I gave up on the maintenance plan cleanup task and wrote my own procedure, using "xp_cmdshell 'dir <path>\*.BAK /s /b /a-a" to get a list of backup files that have been written to tape. I join that to the backup history tables to get the backup date and select the files to delete.

Unfortunately I ran into a bug with SQL 2005 x64 SP1 that causes security problems with jobs containing xp_cmdshell, so I just gave up on the maintenance plan and do it all with custom procedures. I probably would give up on the maintenance plans anyway because I don't do standard backups, I do striped backups to multiple local drives on the server and then move the files to a network share. This is many times faster than "BACKUP DATABASE xxx TO DISK=\\server\share", at least on my hardware, but there is no maintenance plan task to do anything that creative.

Microsoft people told me the security bug would be fixed in SP2, I'm sorry to hear the cleanup task is still broken. It isn't that difficult to write something in T-SQL, VB, or your language of choice that can do the same job, and probably do it better.

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 3

Thanks for replying! I think it is a bug with this. I discovered a major bug tied into this just today. My scripts that I create from my workstation using the SQL Management tool show the time set to do the clean ups as every 1 day where as the same procedure on the server via Management studio shows it to be every 1 hour. I found another script to run cleanups every 4 weeks was showing up on the server as 4 days. When I fixed the server to be every 4 weeks, the same script on my workstation shows as 4 months.-- Why the second script doesn't run I have yet to solve.

I will be submitting a bug report to Microsoft and hopefully they can fix the problem.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Make sure you enter the extension without a dot like TRN and not .TRN

Also make sure that the Agent account or a proxy account if you are not a sysadmin, has permissions to delete files from the folder with logs and your account has a permission to use this proxy.

I did not see the issue with 1 hour versus 1 day. I use Management Studio on both the workstation and the server. What is the "SQL Management tool" that you use?

Regards,Yelena Varsha

MohammedU SSCoach Points: 19644 More actions · Answer 5

You can use xp_delete_file procedure incase of you have issue with cleanup task...

I don't have this kind of issue but I am using 32 bit.

declare @dt datetime

select @dt=getdate()-1

EXECUTE master.dbo.xp_delete_file 0,N'c:\',N'BAK',@dt,1

MohammedU
Microsoft SQL Server MVP

Lowry Kozlowski SSCommitted Points: 1918 More actions · Answer 6

Thanks for all your support people. All problems have been worked out and now functioning correctly. I appreciate the messages.