August 3, 2009 at 3:05 am
Dear Friends,
I am using SQL 2005 SP3. The maintenance plan is not deleting the old files. It backs up the DB fine. But it's not deleting the old files. I have even created another plan ( maintenance Clean up task) just to delete the file, but still no luck. Can someone help?
Many thanks.
August 3, 2009 at 3:06 am
I do apologise i posted it in the wrong forum.. It should have been SQL 2005.
August 3, 2009 at 9:53 am
What is the extension you put in the maintenance cleanup task? Do you have two tasks created, one for your full backups and another for your transaction log backups?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2009 at 3:31 am
What is the extension you put in the maintenance cleanup task?
There are two maintenance cleanup task. One with extension BAK and the other with TRN for deleting files older than 2days. But this is not working.
Do you have two tasks created, one for your full backups and another for your transaction log backups?
No, i have one task which does both the Full Back up and Transaction log Back up. This works fine.
August 4, 2009 at 8:49 am
sarvesh singh (8/4/2009)There are two maintenance cleanup task. One with extension BAK and the other with TRN for deleting files older than 2days. But this is not working.
You don't by chance have the extension setup as .BAK and .TRN do you? If you have the period in there, the task will generate the name with two periods and won't find any of the files to be deleted. Verify also that you don't have a typo in the path.
No, i have one task which does both the Full Back up and Transaction log Back up. This works fine.
I disagree with you here - this does not work fine. There is really no use to backing up the transaction log once a day. You should have a separate job that backs up the transaction log at least every hour - if not more often. I suggest you read the article I link to in my signature about Managing Transaction Logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2009 at 9:21 am
I agree transaction log should be set up more often which i am looking into, but that should not stop the other maintenance plan to delete the old backup files.
I have double checked the extension it's BAK and not .BAK and it's TRN and not .TRN I have checked the path as well. That is fine as well. If i copy the path set up on Maintenance plan and paste it in explorer, i can see the back up files.
August 4, 2009 at 9:31 am
Okay, now let's open the maintenance cleanup task and click on View T-SQL. Copy & paste that here and also into a query window. I would like to see what SQL Server is generating - and you can run that code manually to see if there are any errors.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2009 at 3:29 am
EXECUTE master.dbo.xp_delete_file 0,N'E:\DATA\MSSQL.1\MSSQL\Backup\ ',N'bak',N'2009-08-03T11:09:59',1
I don't get an error message. It says command completed successfully.
August 5, 2009 at 7:46 am
sarvesh singh (8/5/2009)
EXECUTE master.dbo.xp_delete_file 0,N'E:\DATA\MSSQL.1\MSSQL\Backup\ ',N'bak',N'2009-08-03T11:09:59',1I don't get an error message. It says command completed successfully.
Modify the plan and remove the extra space at the end. If that doesn't work - remove the last '\' and see if that works.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2009 at 8:41 am
I have done that, Still the files exist. I don't get any error message.
August 5, 2009 at 10:02 am
We had this same problem with log (.txt) files, and never did find a solution.
You could try running the following as a Schedule Task:
@echo off
echo.
echo Removing old bak files
forfiles /p E:\MSSQL.1\MSSQL\Backup /s /m MyDBNameHere*.bak /d -7 /c "cmd /c del @path"
The "/d -7" deletes files older than 7 days. You can change this as desired.
August 5, 2009 at 10:34 am
Not sure what to tell you, I am using that procedure for versions from 2005 SP2 on up through 2008 with no problems.
If you are not getting any errors and there are no files being deleted then I would have to say that one of the following is happening:
1) The files are in the root directory, you have specified that the procedure should include sub-directories but there are no sub-directories.
2) The files in the directory that you are expecting to be deleted are not SQL Server backup files. The procedure validates that the files are valid and if they were created using a non-native tool (e.g. Litespeed), this tool won't remove those backups.
Try changing the last parameter on the command from a 1 to a 0 and execute it again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2009 at 11:15 am
Does the backup exist within the backup history in SQL?
I have been looking around to try to find this on Google, and if someone can point me to the site with this information would appreciate it, but if you use the clean-up maintenance task does it check for the backup in the msdb backup history tables? To find out 1) if the backup file exist for the databse, and then 2) when the backup file was taken to know if it is older than 1 day or whatever. If the backup does not exist in these tables will it still delete the file? If it does (or is supposed to still delete the file) what does SQL check to see how old the file is, just the windows file properties of date created?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 5, 2009 at 12:56 pm
Melton (8/5/2009)
Does the backup exist within the backup history in SQL?I have been looking around to try to find this on Google, and if someone can point me to the site with this information would appreciate it, but if you use the clean-up maintenance task does it check for the backup in the msdb backup history tables?
To find out 1) if the backup file exist for the databse, and then 2) when the backup file was taken to know if it is older than 1 day or whatever. If the backup does not exist in these tables will it still delete the file? If it does (or is supposed to still delete the file) what does SQL check to see how old the file is, just the windows file properties of date created?
Don't know of any sites where this is documented, but I can tell you that it does not look in any of the tables in MSDB.
The maintenance cleanup task simply calls an extended procedure and uses a generated date/time parameter based upon when the task runs.
What the extended procedure does is opens each file and verifies whether or not that file was created by a SQL Server and is a valid backup format before it deletes the file. You can test this very easily by created a text file with a .bak extension and then trying to delete the file using the maintenance cleanup task. The text file will not be deleted.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2009 at 1:34 pm
1) The files are in the root directory, you have specified that the procedure should include sub-directories but there are no sub-directories.
The files are in the Backup Directory. The backup maintenance task is backing up the files successfully. I have tried deleting these files by selecting the flag 'include sub-directories' and without selecting the flag. But it's not deleting the files.
2) The files in the directory that you are expecting to be deleted are not SQL Server backup files. The procedure validates that the files are valid and if they were created using a non-native tool (e.g. Litespeed), this tool won't remove those backups.
The backup is done using the SQL maintenance task.
Also i have tried it with 0 at the end of the script. Still no Luck.
I am a bit lost.. It looks like i'll have to write a script and schedule it as a job.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply