| Groups | Blog | Home |
sql server reporting services : ChunkData table is growing!
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. -------------------- [quoted text, click to view] > 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,
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]) [quoted text, click to view] 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. -------------------- [quoted text, click to view] > From: Camel <hamishmurphy@gmail.com> Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnap> 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 > shots(Int32& [quoted text, click to view] > 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]) [quoted text, click to view] 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. -------------------- [quoted text, click to view] > 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]) [quoted text, click to view] 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. > > > --------------------
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |