June 8, 2017 at 8:25 am
I have several cleanup jobs that have been working perfectly for years. But lately, some of them quit deleting old files, and some of them seem to be working, but not deleting real old files (example: it's set to delete 24 hours old - it seems to be doing that, but there are file several days older always left intact and never got deleted.)
These are all subplans under the same maintenance package. Some of the cleanup jobs still work just fine.
This just totally baffles me.
June 8, 2017 at 8:52 am
Michelle-138172 - Thursday, June 8, 2017 8:25 AMI have several cleanup jobs that have been working perfectly for years. But lately, some of them quit deleting old files, and some of them seem to be working, but not deleting real old files (example: it's set to delete 24 hours old - it seems to be doing that, but there are file several days older always left intact and never got deleted.)These are all subplans under the same maintenance package. Some of the cleanup jobs still work just fine.
This just totally baffles me.
If the extension is fine (bak only) and nothing has changed with the backups themselves, nothing has moved, etc then it could be that there was a problem with the backup file itself. You could try testing it by manually executing xp_delete_file - view the t-sql for the cleanup task and execute that.
Somewhat related - this is an interesting post on the subject. It says that the extended stored procedure tries to read the header of the files which could possible be related to your situation:
When xp_delete_file doesn’t work as expected…
Sue
June 8, 2017 at 9:41 am
Sue_H - Thursday, June 8, 2017 8:52 AMIf the extension is fine (bak only) and nothing has changed with the backups themselves, nothing has moved, etc then it could be that there was a problem with the backup file itself. You could try testing it by manually executing xp_delete_file - view the t-sql for the cleanup task and execute that.
Somewhat related - this is an interesting post on the subject. It says that the extended stored procedure tries to read the header of the files which could possible be related to your situation:
When xp_delete_file doesn’t work as expected…Sue
Thanks, Sue. I did try the manual tsql of xp_delete_file and it didn't work either. I'm going to accept that maybe some of the old files have bad headers and can't be properly deleted.
Another odd thing is with my trans log deletion. When i found out it was not deleting last week, I disabled backup part temporarily and just ran the deletion and it worked. But now I try the same trick, it wouldn't work.
June 8, 2017 at 10:39 am
Michelle-138172 - Thursday, June 8, 2017 9:41 AMThanks, Sue. I did try the manual tsql of xp_delete_file and it didn't work either. I'm going to accept that maybe some of the old files have bad headers and can't be properly deleted.Another odd thing is with my trans log deletion. When i found out it was not deleting last week, I disabled backup part temporarily and just ran the deletion and it worked. But now I try the same trick, it wouldn't work.
Those are weird. Another thing you may want to try is to do a restore headeronly on the files that don't delete and see if SQL Server can read that much of the file.
Sue
June 8, 2017 at 1:39 pm
Do you have an antivirus installed on the system?
I know our antivirus sometimes catches the files when we go to delete them and ends up locking it while it scans it.
We caught most of these and have it disabled now for 99% of the cases, but every now and then we see one or 2 get caught.
When you ran the command from TSQL, what error did you get while trying to delete the files? That may help figure out why some are not being removed.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 9, 2017 at 10:46 am
bmg002 - Thursday, June 8, 2017 1:39 PMDo you have an antivirus installed on the system?
I know our antivirus sometimes catches the files when we go to delete them and ends up locking it while it scans it.We caught most of these and have it disabled now for 99% of the cases, but every now and then we see one or 2 get caught.
When you ran the command from TSQL, what error did you get while trying to delete the files? That may help figure out why some are not being removed.
Thanks for the input. We do have an antivirus installed, but it doesn't make any sense it's catching up the same files all the time. Also these jobs have been working fine for years and just recently some of them not working.
When I ran TSQL, there was no error though it didn't delete either. There are no errors from any SQL jobs either.
June 9, 2017 at 11:49 am
Michelle-138172 - Friday, June 9, 2017 10:46 AMbmg002 - Thursday, June 8, 2017 1:39 PMDo you have an antivirus installed on the system?
I know our antivirus sometimes catches the files when we go to delete them and ends up locking it while it scans it.We caught most of these and have it disabled now for 99% of the cases, but every now and then we see one or 2 get caught.
When you ran the command from TSQL, what error did you get while trying to delete the files? That may help figure out why some are not being removed.
Thanks for the input. We do have an antivirus installed, but it doesn't make any sense it's catching up the same files all the time. Also these jobs have been working fine for years and just recently some of them not working.
When I ran TSQL, there was no error though it didn't delete either. There are no errors from any SQL jobs either.
No errors seems strange. Pretty sure if you try to delete a file that you don't have permission to or delete a file that doesn't exist, you should get some sort of error message.
With the delete TSQL command, I am assuming this is using XP_cmdshell, correct? If so, do you get any results when replacing the "DEL" with "DIR"? I am expecting the command to be something like "DEL C:\temp\*.log" correct? If so, it could be file permissions or attributes set on the file (hidden and system attributes will make wildcard matching fail).
And with the antivirus, since you get no errors, it likely isn't the antivirus. But depending on the Antivirus settings, it could be doing an on-access scan which could create a temporary lock on the file. I've seen it before on my home PC where I'll delete a folder and it'll chug along happily and then get stuck because a file is in use. I wait a few seconds and hit retry and then it will continue. Checking the AV logs, it saw data changes on the folder and tried to scan the folder due to the changes which created a lock.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 9, 2017 at 12:19 pm
bmg002 - Friday, June 9, 2017 11:49 AMMichelle-138172 - Friday, June 9, 2017 10:46 AMbmg002 - Thursday, June 8, 2017 1:39 PMDo you have an antivirus installed on the system?
I know our antivirus sometimes catches the files when we go to delete them and ends up locking it while it scans it.We caught most of these and have it disabled now for 99% of the cases, but every now and then we see one or 2 get caught.
When you ran the command from TSQL, what error did you get while trying to delete the files? That may help figure out why some are not being removed.
Thanks for the input. We do have an antivirus installed, but it doesn't make any sense it's catching up the same files all the time. Also these jobs have been working fine for years and just recently some of them not working.
When I ran TSQL, there was no error though it didn't delete either. There are no errors from any SQL jobs either.
No errors seems strange. Pretty sure if you try to delete a file that you don't have permission to or delete a file that doesn't exist, you should get some sort of error message.
With the delete TSQL command, I am assuming this is using XP_cmdshell, correct? If so, do you get any results when replacing the "DEL" with "DIR"? I am expecting the command to be something like "DEL C:\temp\*.log" correct? If so, it could be file permissions or attributes set on the file (hidden and system attributes will make wildcard matching fail).And with the antivirus, since you get no errors, it likely isn't the antivirus. But depending on the Antivirus settings, it could be doing an on-access scan which could create a temporary lock on the file. I've seen it before on my home PC where I'll delete a folder and it'll chug along happily and then get stuck because a file is in use. I wait a few seconds and hit retry and then it will continue. Checking the AV logs, it saw data changes on the folder and tried to scan the folder due to the changes which created a lock.
Maintenance plans cleanup task use xp_delete_file. The not getting errors with this issue is typical. It only works with some files as well so it's fairly different from other methods of deleting. It reads the first line of reports to see if it's a maintenance plan report to delete, it reads the file header to see if it's a backup file to delete, etc
Sue
June 9, 2017 at 12:50 pm
Sue_H - Friday, June 9, 2017 12:19 PMbmg002 - Friday, June 9, 2017 11:49 AMMichelle-138172 - Friday, June 9, 2017 10:46 AMbmg002 - Thursday, June 8, 2017 1:39 PMDo you have an antivirus installed on the system?
I know our antivirus sometimes catches the files when we go to delete them and ends up locking it while it scans it.We caught most of these and have it disabled now for 99% of the cases, but every now and then we see one or 2 get caught.
When you ran the command from TSQL, what error did you get while trying to delete the files? That may help figure out why some are not being removed.
Thanks for the input. We do have an antivirus installed, but it doesn't make any sense it's catching up the same files all the time. Also these jobs have been working fine for years and just recently some of them not working.
When I ran TSQL, there was no error though it didn't delete either. There are no errors from any SQL jobs either.
No errors seems strange. Pretty sure if you try to delete a file that you don't have permission to or delete a file that doesn't exist, you should get some sort of error message.
With the delete TSQL command, I am assuming this is using XP_cmdshell, correct? If so, do you get any results when replacing the "DEL" with "DIR"? I am expecting the command to be something like "DEL C:\temp\*.log" correct? If so, it could be file permissions or attributes set on the file (hidden and system attributes will make wildcard matching fail).And with the antivirus, since you get no errors, it likely isn't the antivirus. But depending on the Antivirus settings, it could be doing an on-access scan which could create a temporary lock on the file. I've seen it before on my home PC where I'll delete a folder and it'll chug along happily and then get stuck because a file is in use. I wait a few seconds and hit retry and then it will continue. Checking the AV logs, it saw data changes on the folder and tried to scan the folder due to the changes which created a lock.
Maintenance plans cleanup task use xp_delete_file. The not getting errors with this issue is typical. It only works with some files as well so it's fairly different from other methods of deleting. It reads the first line of reports to see if it's a maintenance plan report to delete, it reads the file header to see if it's a backup file to delete, etc
Sue
Ah... good to know. Shows how often I look at my maintenance plans :/
Did a quick read on that xp_delete_file and if I understand things correctly, it will only delete backup files. So as others indicated, it must read the header of the file ot ensure it is a valid backup. I wonder if there is extra logic going in when it looks to ensure it is the same SQL version?
Could it be it is failing to delete the files because you installed an SP or CU or upgraded to a new version of SQL?
I'm no expert in this, just throwing out some thoughts about why it may be failing to remove the files on you.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 9, 2017 at 1:15 pm
bmg002 - Friday, June 9, 2017 12:50 PMAh... good to know. Shows how often I look at my maintenance plans :/
Did a quick read on that xp_delete_file and if I understand things correctly, it will only delete backup files. So as others indicated, it must read the header of the file ot ensure it is a valid backup. I wonder if there is extra logic going in when it looks to ensure it is the same SQL version?
Could it be it is failing to delete the files because you installed an SP or CU or upgraded to a new version of SQL?
I'm no expert in this, just throwing out some thoughts about why it may be failing to remove the files on you.
No excuse for you...I don't even use the things! 🙂
No one knows much since it's undocumented. They expanded the xp years ago to allow for deleting maintenance plan reports so it also read the first line of those reports. That was only learned when they had a service pack that broke that and it was in the KB article and hot fix for the issue. Prior to that MS had told about the reading of the header to verify the files being backup files and that part became known - from a blog or something I think. Otherwise it's kind of a black hole and you just have to play with it to figure some things out.
I might play around with versions this weekend but I suspect that's not part of it. It's not like this failure to delete doesn't happen much but it's usually related to the parameters used with it. So it is weird when it suddenly fails to delete without any changes. I suspect its something to do with the files themselves and not just when reading the file headers.
Sue
June 9, 2017 at 1:28 pm
Sue_H - Friday, June 9, 2017 1:15 PMbmg002 - Friday, June 9, 2017 12:50 PMAh... good to know. Shows how often I look at my maintenance plans :/
Did a quick read on that xp_delete_file and if I understand things correctly, it will only delete backup files. So as others indicated, it must read the header of the file ot ensure it is a valid backup. I wonder if there is extra logic going in when it looks to ensure it is the same SQL version?
Could it be it is failing to delete the files because you installed an SP or CU or upgraded to a new version of SQL?
I'm no expert in this, just throwing out some thoughts about why it may be failing to remove the files on you.No excuse for you...I don't even use the things! 🙂
No one knows much since it's undocumented. They expanded the xp years ago to allow for deleting maintenance plan reports so it also read the first line of those reports. That was only learned when they had a service pack that broke that and it was in the KB article and hot fix for the issue. Prior to that MS had told about the reading of the header to verify the files being backup files and that part became known - from a blog or something I think. Otherwise it's kind of a black hole and you just have to play with it to figure some things out.
I might play around with versions this weekend but I suspect that's not part of it. It's not like this failure to delete doesn't happen much but it's usually related to the parameters used with it. So it is weird when it suddenly fails to delete without any changes. I suspect its something to do with the files themselves and not just when reading the file headers.Sue
Oh, I know ignorance is not a valid excuse by any means. Which is one of the reasons I come to this forum - to learn.
I have read both in this forum and on other sites and even heard some presenters at PASS and SQL Saturday mention that maintenance plans are bad and that people should be migrating away from them. And Ola Hallengren's scripts are a much better solution than maintenance plans (https://ola.hallengren.com/).
I am in the process of moving ours over, but not quite ready for that yet.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 9, 2017 at 2:06 pm
bmg002 - Friday, June 9, 2017 1:28 PMOh, I know ignorance is not a valid excuse by any means. Which is one of the reasons I come to this forum - to learn.
I have read both in this forum and on other sites and even heard some presenters at PASS and SQL Saturday mention that maintenance plans are bad and that people should be migrating away from them. And Ola Hallengren's scripts are a much better solution than maintenance plans (https://ola.hallengren.com/).
I am in the process of moving ours over, but not quite ready for that yet.
Just giving you grief as you are not at all ignorant.
Ola's solutions are good as are Minionware products - Minion Backup
And there are more people using Powershell as well - dbatool.io has some nice SQL Server scripts.
I've run into quite a few places that won't use Ola or minion backups due to security reasons (sqlcmd, xp_cmdshell). Some places are just that strict on security policies and aren't going to change due to government contracts. It's worse when you go to an Oracle shop and they have a smattering of SQL boxes - they just won't go with anything requiring configuration. And then there are places who don't want to use Agent - only their own enterprise schedulers with basic SQL scripts. So it can be good to know the details of scripting your own and all the cases to manage for whatever environment.
But I would move away from Maintenance plans if I were you - they are just too flaky sometimes. I don't think there is a wrong way to go when/if you move away from them. It's whatever works for you, your company and whatever solution can be supported by multiple people. And it's something to do in all of your free time!
June 9, 2017 at 3:08 pm
Sue_H - Friday, June 9, 2017 1:15 PMbmg002 - Friday, June 9, 2017 12:50 PMAh... good to know. Shows how often I look at my maintenance plans :/
Did a quick read on that xp_delete_file and if I understand things correctly, it will only delete backup files. So as others indicated, it must read the header of the file ot ensure it is a valid backup. I wonder if there is extra logic going in when it looks to ensure it is the same SQL version?
Could it be it is failing to delete the files because you installed an SP or CU or upgraded to a new version of SQL?
I'm no expert in this, just throwing out some thoughts about why it may be failing to remove the files on you.No excuse for you...I don't even use the things! 🙂
No one knows much since it's undocumented. They expanded the xp years ago to allow for deleting maintenance plan reports so it also read the first line of those reports. That was only learned when they had a service pack that broke that and it was in the KB article and hot fix for the issue. Prior to that MS had told about the reading of the header to verify the files being backup files and that part became known - from a blog or something I think. Otherwise it's kind of a black hole and you just have to play with it to figure some things out.
I might play around with versions this weekend but I suspect that's not part of it. It's not like this failure to delete doesn't happen much but it's usually related to the parameters used with it. So it is weird when it suddenly fails to delete without any changes. I suspect its something to do with the files themselves and not just when reading the file headers.Sue
My question is about the files themselves. Can you delete them using DOS or Windows Explorer? If not, you might have a problem.
I ask because I found the same thing just over a year ago. The old backup files were hanging around and I couldn't delete them. It turned out it was corruption on the SAN volume. The SAN admin created a new volume, moved everything that was good and whacked the old one. He took further action on the disk beyond that, but I don't know what it was. This is just something to check into.
June 9, 2017 at 3:19 pm
Ed Wagner - Friday, June 9, 2017 3:08 PMMy question is about the files themselves. Can you delete them using DOS or Windows Explorer? If not, you might have a problem.I ask because I found the same thing just over a year ago. The old backup files were hanging around and I couldn't delete them. It turned out it was corruption on the SAN volume. The SAN admin created a new volume, moved everything that was good and whacked the old one. He took further action on the disk beyond that, but I don't know what it was. This is just something to check into.
Were yours all files on the drive? I know she said hers are sporadic ones here and there.
But if you can't read (or delete) from the disk, you should get errors doing a restore headeronly when that happens - that's why I suggested Michelle try running that. And not sure anyone is going to do that on a Friday.....
Sue
June 9, 2017 at 4:07 pm
Sue_H - Friday, June 9, 2017 3:19 PMEd Wagner - Friday, June 9, 2017 3:08 PMMy question is about the files themselves. Can you delete them using DOS or Windows Explorer? If not, you might have a problem.I ask because I found the same thing just over a year ago. The old backup files were hanging around and I couldn't delete them. It turned out it was corruption on the SAN volume. The SAN admin created a new volume, moved everything that was good and whacked the old one. He took further action on the disk beyond that, but I don't know what it was. This is just something to check into.
Were yours all files on the drive? I know she said hers are sporadic ones here and there.
But if you can't read (or delete) from the disk, you should get errors doing a restore headeronly when that happens - that's why I suggested Michelle try running that. And not sure anyone is going to do that on a Friday.....Sue
Touche, Sue. Mine problem was not with all files on the drive. There were a couple dozen files on the backup volume that were impervious to anything - rename, delete, etc. The rest of the files were fine. The SAN admin was able to save everything I needed to keep.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply