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
>

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

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

checkpoint

Hi friends,
I came across the recommendation to do checkpoint to flush a buffer pool out
when doing massive modification in small chunks under simple recovery mode.
Can someone elaborate a bit more on it? Is it reasonable? And why?
Thanks a lot in advance.
AlexAlex,
This is my understanding...
When you execute a CHECKPOINT Statement it forces SQL Server to write
all dirty pages to Disk. (Dirty pages being Data/Log Pages that have
been modified but not written to Disk). Once the Checkpoint is issued
the Transaction Log is Truncated. SQL Server marks the transaction log
at the start of the active portion i.e Where there are still active
transactions. The rest of log is discarded as all transactions have
either had commit or rollback statement issued.
To be honest I can't really see the need to do this when you are using
Simple Recovery anyway - unless space is an issue. SQL Server issues
Checkpoint commands automatically, so the Log would return to the
minimum size anyway. I think SQL Server issues Checkpoint commands
every 10 mins - I think!
Hope this helps
Barry|||thanks Barry,
That was my understanding too, I just thought maybe I was missing
something,- such recommendation couldn't emerge out of nowhere, right?
Alex
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138908677.612627.55190@.g43g2000cwa.googlegroups.com...
> Alex,
> This is my understanding...
> When you execute a CHECKPOINT Statement it forces SQL Server to write
> all dirty pages to Disk. (Dirty pages being Data/Log Pages that have
> been modified but not written to Disk). Once the Checkpoint is issued
> the Transaction Log is Truncated. SQL Server marks the transaction log
> at the start of the active portion i.e Where there are still active
> transactions. The rest of log is discarded as all transactions have
> either had commit or rollback statement issued.
> To be honest I can't really see the need to do this when you are using
> Simple Recovery anyway - unless space is an issue. SQL Server issues
> Checkpoint commands automatically, so the Log would return to the
> minimum size anyway. I think SQL Server issues Checkpoint commands
> every 10 mins - I think!
> Hope this helps
> Barry
>|||Well, there's no harm in forcing checkpoints in between batches. I
think the recommendation comes from the problems around long running
transactions and large data modifications.
Even in SIMPLE recovery mode the transaction log can grow very large
when there is some long running transaction (thereby preventing the
checkpoint from occurring, because the server can't flush an open
transaction) and it's making lots of changes (thereby producing many log
records). By breaking up the single big transaction into many smaller
batches, that gives the server a chance to checkpoint in between batches
thereby keeping the transaction log under control (and not locking out
other users for long periods of time).
The server will do automatic checkpoints based on a formula (it's not
just every x seconds). It's different depending on the recovery mode.
In simple recovery mode the server will do an automatic checkpoint when
the log becomes 70% full or the number of log records is more than what
SQL Server estimates it can get through in the recovery interval period
(which ever comes sooner). So it's possible that the log is not quite
full enough to do an auto checkpoint at the time you start the next
batch. In which case the log will then continue to be filled and quite
possibly have to do an autogrow operation (at which time the 70% full
mark is even bigger). So there's no harm in doing a manual checkpoint
in between batches and possible benefit.
Personally, when I have to do this kind of stuff I always do manual
checkpoints between batches (although I do it mostly on DBs in full
recovery mode that have their logs backed up pretty regularly - like
every 15 minutes for example - so it's slightly different, but the same
concept).
*mike hodgson*
http://sqlnerd.blogspot.com
AlexM wrote:

>thanks Barry,
>That was my understanding too, I just thought maybe I was missing
>something,- such recommendation couldn't emerge out of nowhere, right?
>Alex
>
>"Barry" <barry.oconnor@.singers.co.im> wrote in message
>news:1138908677.612627.55190@.g43g2000cwa.googlegroups.com...
>
>
>|||I've had some discussions with Storage engine people at MS about this. I've
had cases where forcing
a checkpoint in simple recovery with *not* long running transaction seemed t
o increate re-use of the
log. But this shouldn't happen, and the person I spoke to would consider suc
h cases a bug. The way I
interpreted it: If we can provide a repro, and provide them with that, they
will have a look at it
and "fix" it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23NPOQWGKGHA.668@.TK2MSFTNGP11.p
hx.gbl...
> Well, there's no harm in forcing checkpoints in between batches. I
> think the recommendation comes from the problems around long running
> transactions and large data modifications.
> Even in SIMPLE recovery mode the transaction log can grow very large
> when there is some long running transaction (thereby preventing the
> checkpoint from occurring, because the server can't flush an open
> transaction) and it's making lots of changes (thereby producing many log
> records). By breaking up the single big transaction into many smaller
> batches, that gives the server a chance to checkpoint in between batches
> thereby keeping the transaction log under control (and not locking out
> other users for long periods of time).
> The server will do automatic checkpoints based on a formula (it's not
> just every x seconds). It's different depending on the recovery mode.
> In simple recovery mode the server will do an automatic checkpoint when
> the log becomes 70% full or the number of log records is more than what
> SQL Server estimates it can get through in the recovery interval period
> (which ever comes sooner). So it's possible that the log is not quite
> full enough to do an auto checkpoint at the time you start the next
> batch. In which case the log will then continue to be filled and quite
> possibly have to do an autogrow operation (at which time the 70% full
> mark is even bigger). So there's no harm in doing a manual checkpoint
> in between batches and possible benefit.
> Personally, when I have to do this kind of stuff I always do manual
> checkpoints between batches (although I do it mostly on DBs in full
> recovery mode that have their logs backed up pretty regularly - like
> every 15 minutes for example - so it's slightly different, but the same
> concept).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> AlexM wrote:
>
>|||TIbor, Mike - Thanks for additional comments - further info is always
helpful.
Barry|||>> Even in SIMPLE recovery mode the transaction log can grow very large when
there is some long running transaction (thereby preventing the checkpoint f
rom occurring, because the server can't flush an open transaction)<<
I'm not sure exactly what you mean saying SQL Server can't flush an open tra
nsaction. It's true that the log space can't be reused, but that has nothing
to do with checkpoint. Checkpoint happens at regular intervals, as you sugg
est, but it will write ALL dirty pages to disk, even those that are part of
an uncommitted transaction.
The log space can't be reused, so the log can grow quite large, but the chec
kpoints DO occur.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:%23NPOQWGKGHA.668
@.TK2MSFTNGP11.phx.gbl...
Well, there's no harm in forcing checkpoints in between batches. I think th
e recommendation comes from the problems around long running transactions an
d large data modifications.
Even in SIMPLE recovery mode the transaction log can grow very large when th
ere is some long running transaction (thereby preventing the checkpoint from
occurring, because the server can't flush an open transaction) and it's mak
ing lots of changes (thereby producing many log records). By breaking up th
e single big transaction into many smaller batches, that gives the server a
chance to checkpoint in between batches thereby keeping the transaction log
under control (and not locking out other users for long periods of time).
The server will do automatic checkpoints based on a formula (it's not just e
very x seconds). It's different depending on the recovery mode. In simple
recovery mode the server will do an automatic checkpoint when the log become
s 70% full or the number of log records is more than what SQL Server estimat
es it can get through in the recovery interval period (which ever comes soon
er). So it's possible that the log is not quite full enough to do an auto c
heckpoint at the time you start the next batch. In which case the log will
then continue to be filled and quite possibly have to do an autogrow operati
on (at which time the 70% full mark is even bigger). So there's no harm in
doing a manual checkpoint in between batches and possible benefit.
Personally, when I have to do this kind of stuff I always do manual checkpoi
nts between batches (although I do it mostly on DBs in full recovery mode th
at have their logs backed up pretty regularly - like every 15 minutes for ex
ample - so it's slightly different, but the same concept).
mike hodgson
http://sqlnerd.blogspot.com
AlexM wrote:
thanks Barry,
That was my understanding too, I just thought maybe I was missing
something,- such recommendation couldn't emerge out of nowhere, right?
Alex
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138908677.612627.55190@.g43g2000cwa.googlegroups.com...
Alex,
This is my understanding...
When you execute a CHECKPOINT Statement it forces SQL Server to write
all dirty pages to Disk. (Dirty pages being Data/Log Pages that have
been modified but not written to Disk). Once the Checkpoint is issued
the Transaction Log is Truncated. SQL Server marks the transaction log
at the start of the active portion i.e Where there are still active
transactions. The rest of log is discarded as all transactions have
either had commit or rollback statement issued.
To be honest I can't really see the need to do this when you are using
Simple Recovery anyway - unless space is an issue. SQL Server issues
Checkpoint commands automatically, so the Log would return to the
minimum size anyway. I think SQL Server issues Checkpoint commands
every 10 mins - I think!
Hope this helps
Barry|||Sorry, I shouldn't have used the term "flush". My bad - mixing terminology.
I guess where I was coming from was, and it may since have changed so
I'm prepared to admit I'm wrong, in the old days (with SQL 6.5 and
earlier from memory) the simple recovery model was implemented with the
"trunc. log on checkpoint" dboption (and also depended on the value of
the "bcp/bulk insert" option too). So I was thinking that in simple
recovery mode with SQL 2000 the checkpoint truncates up to the minimum
recovery LSN, but in this case the earliest open transaction represents
the minimum recovery LSN and hence the log cannot be truncated past that
point until those open transactions are complete. (In fact, I just
found the BOL page
<http://msdn.microsoft.com/library/e...ar_da2_8y3y.asp>
that confirms the checkpoint process is responsible for truncating log
records before the MinLSN when the DB is in SIMPLE recovery mode and it
also confirms what I'm trying to say in the "Long-Running Transaction"
section.) And so, while the checkpoint still occurs and flushes all
dirty pages from memory to disk, as far as the size of the physical log
goes, the checkpoint effectively does nothing. I wasn't focused on
memory to disk operations but rather on log truncation operations.
I stand corrected in saying the checkpoint doesn't occur. My point was
that the open transaction would prevent the log from getting truncated
and therefore increased the likelihood of the log needing an autogrow
operation, but you're right (as always) - the checkpoint still occurs.
(I guess I really should have said "thereby preventing the checkpoint
from /truncating the physical log/, because the server can't /truncate
the log record associated with/ an open transaction".)
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
> large when there is some long running transaction (thereby preventing
> the checkpoint from occurring, because the server can't flush an open
> transaction)<<
> I'm not sure exactly what you mean saying SQL Server can't flush an
> open transaction. It's true that the log space can't be reused, but
> that has nothing to do with checkpoint. Checkpoint happens at regular
> intervals, as you suggest, but it will write ALL dirty pages to disk,
> even those that are part of an uncommitted transaction.
> The log space can't be reused, so the log can grow quite large, but
> the checkpoints DO occur.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com <http://www.solidqualitylearning.com>
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:%23NPOQWGKGHA.668@.TK2MSFTNGP11.phx.gbl...
> Well, there's no harm in forcing checkpoints in between batches.
> I think the recommendation comes from the problems around long
> running transactions and large data modifications.
> Even in SIMPLE recovery mode the transaction log can grow very
> large when there is some long running transaction (thereby
> preventing the checkpoint from occurring, because the server can't
> flush an open transaction) and it's making lots of changes
> (thereby producing many log records). By breaking up the single
> big transaction into many smaller batches, that gives the server a
> chance to checkpoint in between batches thereby keeping the
> transaction log under control (and not locking out other users for
> long periods of time).
> The server will do automatic checkpoints based on a formula (it's
> not just every x seconds). It's different depending on the
> recovery mode. In simple recovery mode the server will do an
> automatic checkpoint when the log becomes 70% full or the number
> of log records is more than what SQL Server estimates it can get
> through in the recovery interval period (which ever comes
> sooner). So it's possible that the log is not quite full enough
> to do an auto checkpoint at the time you start the next batch. In
> which case the log will then continue to be filled and quite
> possibly have to do an autogrow operation (at which time the 70%
> full mark is even bigger). So there's no harm in doing a manual
> checkpoint in between batches and possible benefit.
> Personally, when I have to do this kind of stuff I always do
> manual checkpoints between batches (although I do it mostly on DBs
> in full recovery mode that have their logs backed up pretty
> regularly - like every 15 minutes for example - so it's slightly
> different, but the same concept).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> AlexM wrote:
>