Clean old backup with retaindays

Hi,
i have 3 sql server, one with sql2000 and other with sql2005.
on each server i have a maintenance plan. this plan save all base to a
network share on another server.
all plans have the option retaindays at 2.
the sql2000 server clean correctly the old backup. but the sql2005 servers
don' t do it.
all the sql service are running under the same account.
i applied patch on a sql2005 at 3152, the other is always at 3042. but
nothing change.
everydays i muste clean manually the backup...retaindays don't work!
someone have an idea about this bug ?
Ty
ChristopheChristophe,
It's not a bug, it's a feature. :) I assume that you are using the
Maintenance Plans. One thing to know is that the option RETAINDAYS does not
actually delete anything, it is just marking the file to tell SQL Server not
to overwrite this file before the retain time is up.
If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
Backup and Transaction Log Backup tabs and make sure that "Remove files
older than" is set.
If you are using SQL 2005 Maintenance plans, you also need to include the
Maintenance Cleanup task to specify file deletions.
RLF
"Christophe" <Christophe@.discussions.microsoft.com> wrote in message
news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> Hi,
> i have 3 sql server, one with sql2000 and other with sql2005.
> on each server i have a maintenance plan. this plan save all base to a
> network share on another server.
> all plans have the option retaindays at 2.
> the sql2000 server clean correctly the old backup. but the sql2005 servers
> don' t do it.
> all the sql service are running under the same account.
> i applied patch on a sql2005 at 3152, the other is always at 3042. but
> nothing change.
> everydays i muste clean manually the backup...retaindays don't work!
> someone have an idea about this bug ?
> Ty
> Christophe|||ty very much ! ;)
"Russell Fields" wrote:
> Christophe,
> It's not a bug, it's a feature. :) I assume that you are using the
> Maintenance Plans. One thing to know is that the option RETAINDAYS does not
> actually delete anything, it is just marking the file to tell SQL Server not
> to overwrite this file before the retain time is up.
> If you are using SQL 2000 Legacy Maintenance Plans, check the Complete
> Backup and Transaction Log Backup tabs and make sure that "Remove files
> older than" is set.
> If you are using SQL 2005 Maintenance plans, you also need to include the
> Maintenance Cleanup task to specify file deletions.
> RLF
>
> "Christophe" <Christophe@.discussions.microsoft.com> wrote in message
> news:C14216B5-32AA-4D94-972C-122D864DEA51@.microsoft.com...
> > Hi,
> >
> > i have 3 sql server, one with sql2000 and other with sql2005.
> > on each server i have a maintenance plan. this plan save all base to a
> > network share on another server.
> > all plans have the option retaindays at 2.
> > the sql2000 server clean correctly the old backup. but the sql2005 servers
> > don' t do it.
> > all the sql service are running under the same account.
> > i applied patch on a sql2005 at 3152, the other is always at 3042. but
> > nothing change.
> >
> > everydays i muste clean manually the backup...retaindays don't work!
> > someone have an idea about this bug ?
> >
> > Ty
> > Christophe
>
>

Checking Merge agent status in SQL2005

Hi sql gurus,
Is there any way to check the status of the merge agent job per subscriber
in SQL 2005?
Below is the same check for SQL 2000:
use distribution
select runstatus from MSmerge_history as h
where h.timestamp in (select max(timestamp) from MSmerge_history where
agent_id =
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'<subscriber_server_name>')) )
I discovered the solution
use distribution
select mh.*, isnull(ms.runstatus,0) as runstatus
from dbo.MSmerge_history mh with (READPAST), dbo.MSmerge_sessions ms with
(READPAST)
where mh.timestamp in (select max(timestamp) from MSmerge_history where
agent_id in
( select top 1 id from msmerge_agents where subscriber_id =
(select srvid from master..sysservers where srvname =
'subscriber_server_name'))) and ms.agent_id = mh.agent_id