ChunkData table is growing!

Hi,
I have a report that refers to lots of external images (200+ each
run). I've been taking a snapshot of this report so that it's quicker
for users. I've limited the history to 1 snapshot. However, it appears
that the images are being cached in the ChunkData table (in the
ReportServer database) and that the data isn't automatically purged
once the snapshot no longer exists. Is there any way to manually purge
unused data?
Thanks,
CamelIf you look in the Reporting Services log files you should see a line like
this:
w3wp!library!1!10/2/2007-09:50:47:: i INFO: Cleaned 0 batch records, 0
policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running
jobs, 0 persisted streams
Thats the process that runs that will clean up those tables. If those items
are no longer needed then you'll see "15 chunks" cleaned or some such in
that log message. If they are necessary then they will not be cleaned.
Most of the stuff in the tables in the TempDB database can be cleaned out
without hurting anything though so if you want you can shutdown the SRS
Service and wipe those tables clean and you should be ok.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Camel <hamishmurphy@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: ChunkData table is growing!
> Date: Fri, 05 Oct 2007 05:17:09 -0000
> Organization: http://groups.google.com
> Hi,
> I have a report that refers to lots of external images (200+ each
> run). I've been taking a snapshot of this report so that it's quicker
> for users. I've limited the history to 1 snapshot. However, it appears
> that the images are being cached in the ChunkData table (in the
> ReportServer database) and that the data isn't automatically purged
> once the snapshot no longer exists. Is there any way to manually purge
> unused data?
> Thanks,
> Camel
>|||Hi Chris,
Thanks for your reply. I've checked the log out and found the
following exception:
3wp!dbcleanup!1e68!8/10/2007-16:30:20:: e ERROR: Sql Error in
CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Timeout
expired. The timeout period elapsed prior to completion of the
operation or the server is not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()
at
Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32&
chunksCleaned)
w3wp!library!1e68!8/10/2007-16:30:20:: i INFO: Cleaned 0 batch
records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
Any ideas why this would be the case?
Camel
On Oct 6, 12:55 am, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> If you look in the Reporting Services log files you should see a line like
> this:
> w3wp!library!1!10/2/2007-09:50:47:: i INFO: Cleaned 0 batch records, 0
> policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running
> jobs, 0 persisted streams
> Thats the process that runs that will clean up those tables. If those items
> are no longer needed then you'll see "15 chunks" cleaned or some such in
> that log message. If they are necessary then they will not be cleaned.
> Most of the stuff in the tables in the TempDB database can be cleaned out
> without hurting anything though so if you want you can shutdown the SRS
> Service and wipe those tables clean and you should be ok.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > From: Camel <hamishmur...@.gmail.com>
> > Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > Subject: ChunkData table is growing!
> > Date: Fri, 05 Oct 2007 05:17:09 -0000
> > Organization:http://groups.google.com
> > Hi,
> > I have a report that refers to lots of external images (200+ each
> > run). I've been taking a snapshot of this report so that it's quicker
> > for users. I've limited the history to 1 snapshot. However, it appears
> > that the images are being cached in the ChunkData table (in the
> > ReportServer database) and that the data isn't automatically purged
> > once the snapshot no longer exists. Is there any way to manually purge
> > unused data?
> > Thanks,
> > Camel|||That usually means you are having a performance issue on the SQL Server
database that is causing it not to return data in the 30 second command
timeout window. It could also be blocking on the SQL Server as well.
Check and see if there are any SPIDs out there that are blocking other
processes.
I'd check your SQL Server Activity and see if there is anything that could
be causing this.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Camel <hamishmurphy@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Re: ChunkData table is growing!
> Date: Mon, 08 Oct 2007 23:20:17 -0000
> Hi Chris,
> Thanks for your reply. I've checked the log out and found the
> following exception:
> 3wp!dbcleanup!1e68!8/10/2007-16:30:20:: e ERROR: Sql Error in
> CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Timeout
> expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteReader()
> at
> Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader()
> at
>
Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnap
shots(Int32&
> chunksCleaned)
> w3wp!library!1e68!8/10/2007-16:30:20:: i INFO: Cleaned 0 batch
> records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> Any ideas why this would be the case?
> Camel
> On Oct 6, 12:55 am, cal...@.online.microsoft.com (Chris Alton [MSFT])
> wrote:
> > If you look in the Reporting Services log files you should see a line
like
> > this:
> > w3wp!library!1!10/2/2007-09:50:47:: i INFO: Cleaned 0 batch records, 0
> > policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running
> > jobs, 0 persisted streams
> >
> > Thats the process that runs that will clean up those tables. If those
items
> > are no longer needed then you'll see "15 chunks" cleaned or some such in
> > that log message. If they are necessary then they will not be cleaned.
> >
> > Most of the stuff in the tables in the TempDB database can be cleaned
out
> > without hurting anything though so if you want you can shutdown the SRS
> > Service and wipe those tables clean and you should be ok.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> >
> > > From: Camel <hamishmur...@.gmail.com>
> > > Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > > Subject: ChunkData table is growing!
> > > Date: Fri, 05 Oct 2007 05:17:09 -0000
> > > Organization:http://groups.google.com
> >
> > > Hi,
> >
> > > I have a report that refers to lots of external images (200+ each
> > > run). I've been taking a snapshot of this report so that it's quicker
> > > for users. I've limited the history to 1 snapshot. However, it appears
> > > that the images are being cached in the ChunkData table (in the
> > > ReportServer database) and that the data isn't automatically purged
> > > once the snapshot no longer exists. Is there any way to manually purge
> > > unused data?
> >
> > > Thanks,
> >
> > > Camel
>
>|||Hi Chris,
I ran CleanOrphanedSnapshots manually with an Actual Execution Plan.
The query takes between 5 and 6 minutes. The bottleneck is in the
second query:
DELETE ChunkData FROM ChunkData INNER JOIN #tempSnapshot ON
ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID
The query takes 91% of the total execution time. 31% of this statement
is spent on a Sort, 31% on a Distinct Sort and 29% on a Clustered
Index Seek. It's deleting 22000+ records. Can you make any suggestions
to improve the performance of deleting records from the ChunkData
table?
Camel
On Oct 10, 1:37 am, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> That usually means you are having a performance issue on the SQL Server
> database that is causing it not to return data in the 30 second command
> timeout window. It could also be blocking on the SQL Server as well.
> Check and see if there are any SPIDs out there that are blocking other
> processes.
> I'd check your SQL Server Activity and see if there is anything that could
> be causing this.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
>|||Unfortunately SQL Performance isn't my area of expertise. Try the regular
SQL Database newsgroup and see if they can give you any pointers there.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: Camel <hamishmurphy@.gmail.com>
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Re: ChunkData table is growing!
> Date: Thu, 11 Oct 2007 23:40:10 -0000
> Organization: http://groups.google.com
> Lines: 34
> Hi Chris,
> I ran CleanOrphanedSnapshots manually with an Actual Execution Plan.
> The query takes between 5 and 6 minutes. The bottleneck is in the
> second query:
> DELETE ChunkData FROM ChunkData INNER JOIN #tempSnapshot ON
> ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID
> The query takes 91% of the total execution time. 31% of this statement
> is spent on a Sort, 31% on a Distinct Sort and 29% on a Clustered
> Index Seek. It's deleting 22000+ records. Can you make any suggestions
> to improve the performance of deleting records from the ChunkData
> table?
> Camel
> On Oct 10, 1:37 am, cal...@.online.microsoft.com (Chris Alton [MSFT])
> wrote:
> > That usually means you are having a performance issue on the SQL Server
> > database that is causing it not to return data in the 30 second command
> > timeout window. It could also be blocking on the SQL Server as well.
> >
> > Check and see if there are any SPIDs out there that are blocking other
> > processes.
> > I'd check your SQL Server Activity and see if there is anything that
could
> > be causing this.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> >
>|||I'll see what I can find out. Thanks for your help.
On Oct 13, 5:23 am, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> Unfortunately SQL Performance isn't my area of expertise. Try the regular
> SQL Database newsgroup and see if they can give you any pointers there.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > From: Camel <hamishmur...@.gmail.com>
> > Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > Subject: Re: ChunkData table is growing!
> > Date: Thu, 11 Oct 2007 23:40:10 -0000
> > Organization:http://groups.google.com
> > Lines: 34
> > Hi Chris,
> > I ran CleanOrphanedSnapshots manually with an Actual Execution Plan.
> > The query takes between 5 and 6 minutes. The bottleneck is in the
> > second query:
> > DELETE ChunkData FROM ChunkData INNER JOIN #tempSnapshot ON
> > ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID
> > The query takes 91% of the total execution time. 31% of this statement
> > is spent on a Sort, 31% on a Distinct Sort and 29% on a Clustered
> > Index Seek. It's deleting 22000+ records. Can you make any suggestions
> > to improve the performance of deleting records from the ChunkData
> > table?
> > Camel
> > On Oct 10, 1:37 am, cal...@.online.microsoft.com (Chris Alton [MSFT])
> > wrote:
> > > That usually means you are having a performance issue on the SQL Server
> > > database that is causing it not to return data in the 30 second command
> > > timeout window. It could also be blocking on the SQL Server as well.
> > > Check and see if there are any SPIDs out there that are blocking other
> > > processes.
> > > I'd check your SQL Server Activity and see if there is anything that
> could
> > > be causing this.
> > > --
> > > Chris Alton, Microsoft Corp.
> > > SQL Server Developer Support Engineer
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > --