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 sto
p
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/Forum...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 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
>