Maintenance Plan File Deletion Bug

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

Maintenance - Integrity Check failure

Found log for job:
'Error: 4062, Cannot open user default database '<ID>'.
Using master db instead.'
Will check Microsoft Error list.

>--Original Message--
>I've got a database in SQL Server 7/Windows 2000 that is
>maintained through 'Database Maintenance Plans'.
>Integrity of the database is checked once a week
>using 'Integrity Check'. The weekly 'Integrity Check'
is
>failing. The error message given is 'The job has failed
>in step one, please review step 1' and nothing is put
>into the application event log. The transaction sql
>command for checking the integrity check of a database
is
>DBCC Checkdb('database'). The command line integrity
>check works fine and returns no errors.
>Failures are also occuring for 'Update Statistics used
by
>query optimizer' and 'Reorganize data and index pages'
in
>Maintenance.
>What is causing these maintenance plans to fail?
>.
>If you get this when SQL Agent logs in to run a job then
most likely the default database for the SQL Agent service
account is assigned to a database that no longer exists on
the server. You may want to check the default database for
that account. You'd probably want it set to master.
-Sue
On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
<anonymous@.discussions.microsoft.com> wrote:
>Found log for job:
>'Error: 4062, Cannot open user default database '<ID>'.
>Using master db instead.'
>Will check Microsoft Error list.
>
>is
>is
>by
>in|||You should also not automatically repair errors. You shoudl always do
root-cause analysis as these errors are usually an indication that you have
a hardware problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:73ma70t1m49tgqnqmk0ccuiopd3qd4n4rj@.
4ax.com...
> If you get this when SQL Agent logs in to run a job then
> most likely the default database for the SQL Agent service
> account is assigned to a database that no longer exists on
> the server. You may want to check the default database for
> that account. You'd probably want it set to master.
> -Sue
> On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
> <anonymous@.discussions.microsoft.com> wrote:
>
>

Maintenance - Integrity Check failure

Found log for job:
'Error: 4062, Cannot open user default database '<ID>'.
Using master db instead.'
Will check Microsoft Error list.

>--Original Message--
>I've got a database in SQL Server 7/Windows 2000 that is
>maintained through 'Database Maintenance Plans'.
>Integrity of the database is checked once a week
>using 'Integrity Check'. The weekly 'Integrity Check'
is
>failing. The error message given is 'The job has failed
>in step one, please review step 1' and nothing is put
>into the application event log. The transaction sql
>command for checking the integrity check of a database
is
>DBCC Checkdb('database'). The command line integrity
>check works fine and returns no errors.
>Failures are also occuring for 'Update Statistics used
by
>query optimizer' and 'Reorganize data and index pages'
in
>Maintenance.
>What is causing these maintenance plans to fail?
>.
>
If you get this when SQL Agent logs in to run a job then
most likely the default database for the SQL Agent service
account is assigned to a database that no longer exists on
the server. You may want to check the default database for
that account. You'd probably want it set to master.
-Sue
On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
<anonymous@.discussions.microsoft.com> wrote:
>Found log for job:
>'Error: 4062, Cannot open user default database '<ID>'.
>Using master db instead.'
>Will check Microsoft Error list.
>
>is
>is
>by
>in
|||You should also not automatically repair errors. You shoudl always do
root-cause analysis as these errors are usually an indication that you have
a hardware problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:73ma70t1m49tgqnqmk0ccuiopd3qd4n4rj@.4ax.com...
> If you get this when SQL Agent logs in to run a job then
> most likely the default database for the SQL Agent service
> account is assigned to a database that no longer exists on
> the server. You may want to check the default database for
> that account. You'd probably want it set to master.
> -Sue
> On Wed, 7 Apr 2004 14:24:33 -0700, "Jim"
> <anonymous@.discussions.microsoft.com> wrote:
>