I have a maintenance plan set to delete files after 1 week. Instead it is deleting everthing older then 1 day. When I view the tsql in the designer, the xp_delete_file string looks correct. Is there anyway to see the exact xp_delete_file string that is actually being executed at run time?
I found the problem.
There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.
If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.
The patching procedure is a little complicated. Read this:
http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
What service pack you are on?
There were maintenane related bugs in SP2 so that MS released hot fix on 03/05/2007.
Check the hot fix if you are already on SP2 applied before the hot fix release. If you are on SP1 apply the SP2 down loaded after 03/06/2007 which is hot fix included.
|||I found out what the problem is.
There is an "off by one" bug where the wrong time unit of measure is being used.
"Hours" are being converted to "Days", Days" are being converted to "Hours", and "Hours" will blow with an invalid index.
This problem exists for "Maintenance Cleanup" tasks and "Clean Up History" tasks.
The fix is a little complicated because there are two versions of sp2 out there.
Read this to figure out the proper fix:
http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx
