check sql licence: growing

Showing posts with label growing. Show all posts

Tuesday, March 20, 2012

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.
> > > --

Saturday, February 25, 2012

Checkpoint and TempDB

cbrichards via droptable.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We ru
n
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly s
top
> and restart services.
> One thing I would like information on is the checkpoint process, suspectin
g
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/n
ot
> working?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is no
t
occurring on TempDB, I need to know if there is a way to verify my suspicion
s
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
Message posted via http://www.droptable.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.droptable.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:[vbcol=seagreen]
>To capture CHECKPOINT you need to run profiler.
>
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You can restart SQL Server and it will create a new tempdb database
>
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
> --
> Message posted via http://www.droptable.com
>|||While that is a good link, and I have it bookmarked, my initial question tha
t
started this thread is still not being addressed:
****************************************
************************************
***************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/of
f,
working/not
working?
****************************************
************************************
***************************************
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> ****************************************
**********************************
****************************************
*
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> ****************************************
**********************************
****************************************
*
> Roger Wolter[MSFT] wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200701/1
>

Checkpoint and TempDB

We are running SQL 2005, SP1, on Windows 2003.
Occasionally we have a TempDB log that starts growing exponentially. We run
DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
sysindexes for objects like '#%', but nothing shows its face. This happens
very infrequently, and since nothing definitive is found, we reluctantly stop
and restart services.
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time, if checkpoint is on/off, working/not
working?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1I have a similar problelm. We have some in-house databases and some
that we have unfortunately inherited. One of them pulls in millions of
records into tables that are temporary using #tablename. Our company is
not going to pay to change the application at this point, so I have to
deal with it. What I did is create a simple job that checks the size of
the temp db file. I am on 2000 so I used sysfiles. When the size in our
case exceeds 15 gigs, I just use msdb.db.sp_start_job to kick off a job
to shrink the temp db. I think it probably runs about 3X a week, but
has been working to keep everything under control. I also set a max
size of 20 gigs so it doesn't eat up all the space it has on its drive.
Maybe these ideas will help. At least you wouldn't have to stop/start
services and manually intervene. My shrinks generally occur at 2-4 a.m.
when I am sleeping :)
cbrichards via SQLMonster.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||To capture CHECKPOINT you need to run profiler.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:
>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process is
>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
>>To capture CHECKPOINT you need to run profiler.
>> Thanks Kristina, but that does not address my issue, as I suspect the
>> Checkpoint is not working. Since I suspect that the checkpoint process
>> is
>>[quoted text clipped - 4 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:
>Hi
>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
>> a
>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via http://www.sqlmonster.com|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
>>Hi
>>You can restart SQL Server and it will create a new tempdb database
>> So I am in the middle of a crisis, my tempdb log is growing at about 2
>> gig
>> a
>>[quoted text clipped - 16 lines]
>> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.sqlmonster.com
>|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
*******************************************************************************************************************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
*******************************************************************************************************************
Roger Wolter[MSFT] wrote:
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>> Wow!
>[quoted text clipped - 14 lines]
>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> *******************************************************************************************************************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> *******************************************************************************************************************
> Roger Wolter[MSFT] wrote:
>>Does this help?
>>http://support.microsoft.com/kb/317375/
>>If this is urgent you should open a support case.
>> Wow!
>>[quoted text clipped - 14 lines]
>>>
>>> This Thread is not closed. Please HELP!!
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1
>|||Thanks Uri. I appreciate the info.
Uri Dimant wrote:
>Hi
>> Is there any way to verify at the time of the crisis, if checkpoint is
>> on/off,
>> working/not
>> working?
>1)
>You can monitor the number of pages flushed by a checkpoint
>using PerfMon - SQLServer:Buffer Manager object, Checkpoint
>pages/sec counter
>2)
>You can start SQL Server with the
>traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
>will be recorded in the SQL Server error log, along with the time of the
>checkpoint.
>> While that is a good link, and I have it bookmarked, my initial question
>> that
>[quoted text clipped - 22 lines]
>>>
>>> This Thread is not closed. Please HELP!!
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200701/1

Checkpoint and TempDB

cbrichards via droptable.com wrote:
> We are running SQL 2005, SP1, on Windows 2003.
> Occasionally we have a TempDB log that starts growing exponentially. We run
> DBCC OpenTran across all databases, we look for high rowcounts on tempdb..
> sysindexes for objects like '#%', but nothing shows its face. This happens
> very infrequently, and since nothing definitive is found, we reluctantly stop
> and restart services.
> One thing I would like information on is the checkpoint process, suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of a
> sudden we have rapid growth.
> Is there any way to verify at the time, if checkpoint is on/off, working/not
> working?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Thanks Kristina, but that does not address my issue, as I suspect the
Checkpoint is not working. Since I suspect that the checkpoint process is not
occurring on TempDB, I need to know if there is a way to verify my suspicions
at the time the crisis is occurring.
This Thread is not closed. Please HELP!!
Message posted via http://www.droptable.com
|||To capture CHECKPOINT you need to run profiler.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c32652e33427@.uwe...
> Thanks Kristina, but that does not address my issue, as I suspect the
> Checkpoint is not working. Since I suspect that the checkpoint process is
> not
> occurring on TempDB, I need to know if there is a way to verify my
> suspicions
> at the time the crisis is occurring.
> This Thread is not closed. Please HELP!!
> --
> Message posted via http://www.droptable.com
>
|||So I am in the middle of a crisis, my tempdb log is growing at about 2 gig a
minute, and the quickest way to determine if my CHECKPOINT is working is to
run Profiler?
What EventClass and Columns would I use?
Within those EventClasses and Columns you recommend, what am I looking for?
I take it I would filter the Profiler on DatabaseID = 2?
Uri Dimant wrote:[vbcol=seagreen]
>To capture CHECKPOINT you need to run profiler.
>[quoted text clipped - 4 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi
You can restart SQL Server and it will create a new tempdb database
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c576f9f33a54@.uwe...
> So I am in the middle of a crisis, my tempdb log is growing at about 2 gig
> a
> minute, and the quickest way to determine if my CHECKPOINT is working is
> to
> run Profiler?
> What EventClass and Columns would I use?
> Within those EventClasses and Columns you recommend, what am I looking
> for?
> I take it I would filter the Profiler on DatabaseID = 2?
> Uri Dimant wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
>
|||Wow!
Am I not making sense!!
Please somebody...address my questions.
This case is not closed!!!
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>You can restart SQL Server and it will create a new tempdb database
>[quoted text clipped - 16 lines]
Message posted via http://www.droptable.com
|||Does this help?
http://support.microsoft.com/kb/317375/
If this is urgent you should open a support case.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c60e0873cbc4@.uwe...
> Wow!
> Am I not making sense!!
> Please somebody...address my questions.
> This case is not closed!!!
> Uri Dimant wrote:
> --
> Message posted via http://www.droptable.com
>
|||While that is a good link, and I have it bookmarked, my initial question that
started this thread is still not being addressed:
************************************************** ************************************************** ***************
One thing I would like information on is the checkpoint process, suspecting
that the automatic checkpoint is not occurring in TempDB, and thus, all of a
sudden we have rapid growth.
Is there any way to verify at the time of the crisis, if checkpoint is on/off,
working/not
working?
************************************************** ************************************************** ***************
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
>Does this help?
>http://support.microsoft.com/kb/317375/
>If this is urgent you should open a support case.
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
|||Hi
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
1)
You can monitor the number of pages flushed by a checkpoint
using PerfMon - SQLServer:Buffer Manager object, Checkpoint
pages/sec counter
2)
You can start SQL Server with the
traceflag 3502. With this trace falg on, whenever a checkpoint occurs, it
will be recorded in the SQL Server error log, along with the time of the
checkpoint.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6c62ef5fc5f05@.uwe...
> While that is a good link, and I have it bookmarked, my initial question
> that
> started this thread is still not being addressed:
> ************************************************** ************************************************** ***************
> One thing I would like information on is the checkpoint process,
> suspecting
> that the automatic checkpoint is not occurring in TempDB, and thus, all of
> a
> sudden we have rapid growth.
> Is there any way to verify at the time of the crisis, if checkpoint is
> on/off,
> working/not
> working?
> ************************************************** ************************************************** ***************
> Roger Wolter[MSFT] wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200701/1
>

Tuesday, March 20, 2012

ChunkData table is growing!

Saturday, February 25, 2012

Checkpoint and TempDB

Checkpoint and TempDB

Checkpoint and TempDB

check sql licence

Blog Archive

About Me