I've got a maintenance plan that executes weekly in the off hours. It's always reporting success, but the old backups don't get deleted. I don't want the drive filling up.
我有一个维护计划,在下班时间每周执行一次。它始终报告成功,但旧备份不会被删除。我不希望驱动器充满。
DB Server info: SQL Server Standard Edition 9.00.3042.00
数据库服务器信息:SQL Server标准版9.00.3042.00
There is a "Maintenance Cleanup Task" set to
有一个“维护清理任务”设置为
"Search folder and delete files based on an extension"
“根据扩展程序搜索文件夹并删除文件”
and "Delete files based on the age of the file at task run time" is checked and set to 4 weeks.
和“在任务运行时根据文件的年龄删除文件”被选中并设置为4周。
The only thing I can see is that my backups are each given their own subfolder and that this is not recursive. Am I missing something?
我唯一能看到的是我的备份每个都有自己的子文件夹,这不是递归的。我错过了什么吗?
Also: I have seen the issues pre-SP2, but I am running service pack 2.
另外:我已经看到SP2之前的问题,但我正在运行Service Pack 2。
4 个解决方案
#1
2
If you make your backups in subfolders, you have to specify the exact subfolder for deleting.
如果在子文件夹中进行备份,则必须指定要删除的确切子文件夹。
For example:
You make the backup by choosing the option that says something like "Make one backup file for each database" and check the box that says "Create subfolder for each database". (I work with a German version of SQL Server, so I translate everything into English myself now)
您可以通过选择“为每个数据库创建一个备份文件”之类的选项来进行备份,并选中“为每个数据库创建子文件夹”复选框。 (我使用的是德语版的SQL Server,所以我现在将所有内容翻译成英文)
The specified folder is H:\Backup, so the backups will actually be created in the folder H:\Backup\DatabaseName.
指定的文件夹是H:\ Backup,因此实际上将在文件夹H:\ Backup \ DatabaseName中创建备份。
And if you want the Maintenance Cleanup Task to delete the backups via "Delete files based on the age of the file at task run time", you have to specify the folder H:\Backup\DatabaseName, not H:\Backup !!!
如果您希望维护清理任务通过“在任务运行时根据文件的年龄删除文件”来删除备份,则必须指定文件夹H:\ Backup \ DatabaseName,而不是H:\ Backup !!!
This is the mistake that I made when I started using SQL Server 2005 - I put the same folder in both fields, Backup and Cleanup.
这是我在开始使用SQL Server 2005时犯的错误 - 我将相同的文件夹放在备份和清理这两个字段中。
#2
1
My understanding is that you can only include the first level of subfolders. I am assuming that you have that check-box checked already.
我的理解是你只能包含第一级子文件夹。我假设您已经检查了该复选框。
Are your backups deeper than the just one level?
您的备份是否比仅仅一个级别更深?
Another thought is, do you have one single maintenance plan that you run to delete backups of multiple databases? The reason I ask this is because the way I could see that you would have to do that would be to point it to a folder that was one level higher meaning that your "include first-level subfolders" would not be deep enough.
另一个想法是,您是否有一个单独的维护计划,您运行以删除多个数据库的备份?我问这个的原因是因为我可以看到你必须这样做的方式是将它指向一个更高一级的文件夹,这意味着你的“包括第一级子文件夹”不够深。
The way I have mine set up is that the Maintenance Cleanup Task is part of my backup process. So once the backup completes for a specific database the Maintenance Cleanup Task runs on that same database backup files. This allows me to be more specific on the directory so I don't run into the directory structure being too deep. Since I have the criteria set the way I want, items don't get deleted till I am ready for them to be deleted either way.
我设置的方式是维护清理任务是我备份过程的一部分。因此,一旦特定数据库的备份完成,维护清理任务就会在相同的数据库备份文件上运行。这使我可以更具体地在目录上,所以我不会遇到目录结构太深。由于我按照我想要的方式设置了标准,因此在我准备好删除它们之前,项目不会被删除。
Tim
#3
0
Make sure your maintenance plan does not have any errors associated it with. You can check the error log under the SQL Server Agent area in the SQL Server Management Studio. If there are errors during your maintenance plans, then it is probably quitting before it starts to delete the outdated backups.
确保您的维护计划没有任何与之相关的错误。您可以在SQL Server Management Studio中的SQL Server代理区域下检查错误日志。如果维护计划期间出现错误,则可能在开始删除过期备份之前退出。
#4
0
Another issue could be the "workflow" of the maintenance plan.
另一个问题可能是维护计划的“工作流程”。
If your plan consists of more than one task, you have to connect the tasks with arrows to define the order in which they will run.
如果您的计划包含多个任务,则必须使用箭头连接任务以定义它们的运行顺序。
Possible issue #1:
可能的问题#1:
You forgot to connect them with arrows. I just tested that - the job runs without any error or warning, but it executes only the first task.
你忘了用箭头连接它们。我刚刚测试过 - 作业运行没有任何错误或警告,但它只执行第一个任务。
Possible issue #2:
可能的问题#2:
You defined the workflow in a way that the cleanup task will never run. If you connect two tasks with an arrow, you can right-click on the arrow and specify if the second task will run always or only when the first one does/does not run successful (this changes the color of the arrow, possible are red/green/blue). Maybe the backup works, and then the cleanup never runs because it will only run when the backups fails?
您以清理任务永远不会运行的方式定义了工作流。如果使用箭头连接两个任务,则可以右键单击箭头并指定第二个任务是始终运行还是仅在第一个任务成功/不成功运行时(这会更改箭头的颜色,可能是红色) /绿,蓝)。也许备份工作,然后清理永远不会运行,因为它只会在备份失败时运行?
#1
2
If you make your backups in subfolders, you have to specify the exact subfolder for deleting.
如果在子文件夹中进行备份,则必须指定要删除的确切子文件夹。
For example:
You make the backup by choosing the option that says something like "Make one backup file for each database" and check the box that says "Create subfolder for each database". (I work with a German version of SQL Server, so I translate everything into English myself now)
您可以通过选择“为每个数据库创建一个备份文件”之类的选项来进行备份,并选中“为每个数据库创建子文件夹”复选框。 (我使用的是德语版的SQL Server,所以我现在将所有内容翻译成英文)
The specified folder is H:\Backup, so the backups will actually be created in the folder H:\Backup\DatabaseName.
指定的文件夹是H:\ Backup,因此实际上将在文件夹H:\ Backup \ DatabaseName中创建备份。
And if you want the Maintenance Cleanup Task to delete the backups via "Delete files based on the age of the file at task run time", you have to specify the folder H:\Backup\DatabaseName, not H:\Backup !!!
如果您希望维护清理任务通过“在任务运行时根据文件的年龄删除文件”来删除备份,则必须指定文件夹H:\ Backup \ DatabaseName,而不是H:\ Backup !!!
This is the mistake that I made when I started using SQL Server 2005 - I put the same folder in both fields, Backup and Cleanup.
这是我在开始使用SQL Server 2005时犯的错误 - 我将相同的文件夹放在备份和清理这两个字段中。
#2
1
My understanding is that you can only include the first level of subfolders. I am assuming that you have that check-box checked already.
我的理解是你只能包含第一级子文件夹。我假设您已经检查了该复选框。
Are your backups deeper than the just one level?
您的备份是否比仅仅一个级别更深?
Another thought is, do you have one single maintenance plan that you run to delete backups of multiple databases? The reason I ask this is because the way I could see that you would have to do that would be to point it to a folder that was one level higher meaning that your "include first-level subfolders" would not be deep enough.
另一个想法是,您是否有一个单独的维护计划,您运行以删除多个数据库的备份?我问这个的原因是因为我可以看到你必须这样做的方式是将它指向一个更高一级的文件夹,这意味着你的“包括第一级子文件夹”不够深。
The way I have mine set up is that the Maintenance Cleanup Task is part of my backup process. So once the backup completes for a specific database the Maintenance Cleanup Task runs on that same database backup files. This allows me to be more specific on the directory so I don't run into the directory structure being too deep. Since I have the criteria set the way I want, items don't get deleted till I am ready for them to be deleted either way.
我设置的方式是维护清理任务是我备份过程的一部分。因此,一旦特定数据库的备份完成,维护清理任务就会在相同的数据库备份文件上运行。这使我可以更具体地在目录上,所以我不会遇到目录结构太深。由于我按照我想要的方式设置了标准,因此在我准备好删除它们之前,项目不会被删除。
Tim
#3
0
Make sure your maintenance plan does not have any errors associated it with. You can check the error log under the SQL Server Agent area in the SQL Server Management Studio. If there are errors during your maintenance plans, then it is probably quitting before it starts to delete the outdated backups.
确保您的维护计划没有任何与之相关的错误。您可以在SQL Server Management Studio中的SQL Server代理区域下检查错误日志。如果维护计划期间出现错误,则可能在开始删除过期备份之前退出。
#4
0
Another issue could be the "workflow" of the maintenance plan.
另一个问题可能是维护计划的“工作流程”。
If your plan consists of more than one task, you have to connect the tasks with arrows to define the order in which they will run.
如果您的计划包含多个任务,则必须使用箭头连接任务以定义它们的运行顺序。
Possible issue #1:
可能的问题#1:
You forgot to connect them with arrows. I just tested that - the job runs without any error or warning, but it executes only the first task.
你忘了用箭头连接它们。我刚刚测试过 - 作业运行没有任何错误或警告,但它只执行第一个任务。
Possible issue #2:
可能的问题#2:
You defined the workflow in a way that the cleanup task will never run. If you connect two tasks with an arrow, you can right-click on the arrow and specify if the second task will run always or only when the first one does/does not run successful (this changes the color of the arrow, possible are red/green/blue). Maybe the backup works, and then the cleanup never runs because it will only run when the backups fails?
您以清理任务永远不会运行的方式定义了工作流。如果使用箭头连接两个任务,则可以右键单击箭头并指定第二个任务是始终运行还是仅在第一个任务成功/不成功运行时(这会更改箭头的颜色,可能是红色) /绿,蓝)。也许备份工作,然后清理永远不会运行,因为它只会在备份失败时运行?