Cleanup of Expired Snapshots

Do you have anonymous subscriptions? If so, the snapshots
are kept for the retention period of the subscription to
given anonymous subscriptions time to synchronize.
HTH,
Paul Ibison
Bill,
sp_helpdistributor will confirm the value you report (default is 72 hours)
so it might be worth running it just to check.
If the directorynames/filenames have changed, or if the system clock has
altered then I can understand it.
Regards,
Paul Ibison
|||Paul,
From sp_helpdistributor the max distrib retention is 720
and the min is set to 0. This is 720 hours (30 days). I
believe the system clock has not been altered.
I think the retention is working perfectly related to the
distribution database transactions, so I don't think the
retention value is the issue. It is just the snapshot
files that are not being cleaned up.
Do you know which stored procedure removes the snapshot
files and how it invokes the OS to delete the files?
Bill

>--Original Message--
>Bill,
>sp_helpdistributor will confirm the value you report
(default is 72 hours)
>so it might be worth running it just to check.
>If the directorynames/filenames have changed, or if the
system clock has
>altered then I can understand it.
>Regards,
>Paul Ibison
>
>.
>
|||Bill,
I believe the procedure you're looking for is sp_MSDelete_publisherdb_trans.
You can follow the logic from sp_MSdistribution_cleanup and
sp_MSdistribution_delete.
HTH,
Paul Ibison

Check the finish time of data driven subscription using Sql

How can I check using sql that a data driven subscription has run and the time it had finished?

Thanks

Elias

Hello Elias,

Run this from the server with the Reporting Services database:

select c.name, el.TimeStart, el.TimeEnd, el.TimeDataRetrieval, el.TimeProcessing, el.TimeRendering
from ReportServer.dbo.executionlog el
innerjoin ReportServer.dbo.catalog c on el.reportid = c.itemid
where c.name='ReportName'
orderby timestart desc

Hope this helps.

Jarret

|||

Jarret,

Thanks, that is helpfull. But the question is not at the report level but at the subsctiption level. What I mean is, I would like to pass in a Subscription Description and find out if this subscription finished running.

Elias

|||

Try this:

select c.name, el.TimeStart, el.TimeEnd, el.TimeDataRetrieval, el.TimeProcessing, el.TimeRendering, s.Description
from ReportServer.dbo.executionlog el
innerjoin ReportServer.dbo.catalog c on el.reportid = c.itemid
innerjoin ReportServer.dbo.subscriptions s on c.itemid = s.report_oid
where s.description ='SubscriptionDescription'
orderby el.timestart desc

If there is an entry in the executionlog table, that means it completed.

Jarret

|||

Jarret,

Thanks.

A couple of questions:

1) It seems that the only way to know if this was a success is to parse the ReportServer.dbo.subscriptions.LastStatus for "Done" & "0 Errors"?

2) Is there any way to bring back only those rows in ReportServer.dbo.executionlog that were created by running the the subscription which relates to the ReportServer.dbo.subscriptions.LastRunTime. (is there some Id on the subscription that the executionlog gets tagged with)

Elias

|||

There is a 'status' field on ExecutionLog that says 'rsSuccess' if the job was successful.

Unfortunately, the times don't match and there is no ID on the executionlog to correspond to the subscription. I believe the times are different because when the report is done running, it writes the executionlog, but the email/file delivery has to take place before the subscription is completed. The ExecutionLog.TimeEnd should be fairly close to the Subscriptions.LastRunTime, but not exact. If you run your report every hour, then you can check to see if the two times are within, say, 10 minutes of each other.

Jarret