Showing posts with label checkpoints. Show all posts
Showing posts with label checkpoints. Show all posts

Wednesday, March 7, 2012

Checkpoints: FTP task download FTP files fails in between then what will happen?

Hi,

I have a FTP task in my control flow that download files from a FTP server. This ftp task is inside a foreach container that loops over a ADO recordset for the file name. The files that the ftp task pulls are huge. If the FTP task fails then I want the FTP task to restart and only download those files that have not been downloaded. Is this possible?

What possible configurations do I have to make to the foreach container and the filetask?

Thanks a lot in advance for your help and time.

Regards,

$wapnil

Experts!!!! any inputs on this.

Thanks in advance for your time.

Regards,

$wapnil

|||

anyone.....help!

Thanks,

$wapnil

Checkpoints problem in parallel tasks

Hi,

I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.

The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.

If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.

Please let me know if there is any setting that only the failed task executes on restart.

Thanks in advance

Essentially, you want to track the outcome of parallel execute package tasks, and only re-execute those which have failed. The problem with using checkpoint files to accomplish this, is that checkpoint files don't track the status of parallel containers after the first task failure associated with FailPackageOnFailure happens.

What that means, if you have 10 parallel EPTs (execute package tasks), and any one of them has a task failure, none of the subsequently completed EPT tasks, whether they succeed or fail, have their outcomes written to the checkpoint file. So, on package restart, "post last checkpoint file write" tasks will run again.

An easier approach might be to put a for loop around each EPT, and loop until successful, using a variable scoped at the For Loop as a"Go/No Go" decision maker, and max retry count.

However, if you want to do it in SSIS using a restart mechanism, you could roll your own checkpointing mechanism.

Such a mechanism would mean creating an OnTaskFailed event handler which would track the failed EPT SourceID/SourceName's (read TaskID/TaskName).

Then add an OnPostExecute event handler to determine those EPTs which succeeded by inference (they didn't fail). Add in a final "On Completetion" script task to append the successful TaskIDs to a configuration file which would then be read in automatically on subsequent execution.

Lastly, you'd set the the Disable property on each EPT to something like FINDSTRING(@.SuccessfulTaskIDs,@.System::TaskID,1) > 0. You can do it that way, but its not point and click by any stretch.

CheckPoints and On Completion

I have a package that has 4 Script Tasks that are placed sequentially.

I have Task1--> Task2-->Task3-->Task4

The arrows between them are OnCompletion Arrows as opposed to the Standard OnSuccess arrows.Even if Task2 failed, it would still execute 3 and 4

the catch is that i want it such that when i run the first time and task 2 fails, then all the tasks except task2 should run which is fine, but when i rerun it. I want it such that it realises that task 2 had failed earlier, so it runs just task2.... if both 2 and 4 had failed then it should just run 2 and 4

i tired to implement it with check points, but the problemn is that if it fails at task2 it stops at task2 and does not continue to execute tasks 3 &4... when u rerun it starts at 2 but like i said i would like 3 & 4 to have completed the previous run...

Any suggestions would be helpful

Thanks for any help in advance..

smathew

So do you really need the condition to start the next task? It seems that if you would just like to rerun the tasks that failed you don't REALLY have any constraints on when the tasks should start... Or is this wrong?

If that is correct you can probably just get rid of the constraints and have four unconnected tasks which would (should?) rerun the correct tasks via the checkpoints...

|||

well the scripts call child packages, where the child packages are the same, but they call with different variables, so if they are called in parallel and they execute parallel, then since they load the same set of tables, it could end up in a blocking situation..

smathew

|||

Just an idea, try saving the status of relevant tasks (2, 4) in variable(s) and save those variable(s) in the config file or a flat file at the start read the prevously saved status from that file and based on the value of the variable branch as required.

|||

Hi,

Interesting problem. There's a possible solution here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=464381&SiteID=1

Its a pretty funky little method - very clever indeed.

-Jamie

|||

That is just about the exact solution I came up with after looking at this for the past hour or two...

No fun GUI point and click, but probably the best you will do with this type of situation.

Checkpoints

Hi,
i have a complex database. If a user (database is set to single user)
make changes, and the user want do redo these changes, i need a kind of
manual setted checkpoints. Such a checkpoint is set before changes
made.
The idea is, to set a checkpoint, (store all checkpoints), and when its
necassary you can revert to a checkpoint.
Is this possible or is there another technique to realize this?
Thank you in advance
AlexSQL Server has "database snapshot" that you could potentially use. You can r
estore from a database
snapshot. But there can only exist *one* snapshot of a database if you want
to restore from a (that)
snapshot.
But perhaps it is much cleaner to begin a transaction and rollback?
There's no built-in scheme where many users has many snapshots and you want
to revert to some
certain snapshot for one of the users without affecting the other users (unl
ess each user have their
own database). How would SQL Server understand what data that belongs to use
r A and revert only that
data? Not to speak about relationships between tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>|||Every change which is made, is encapsulated by an transaction and in
errorcase undone by the rollback or if success with commit.
So i want to set a checkpoint at start of editing the tables, and if
the user want to
undo his changes (created new datarows etc.) so the user can go to the
checkpoint, created at start editing.
So it is a single user instance all changes since last checkpoint
should be reverted.
A case of "higher level transaction"
Alex
Tibor Karaszi schrieb:
[vbcol=seagreen]
> SQL Server has "database snapshot" that you could potentially use. You can
restore from a database
> snapshot. But there can only exist *one* snapshot of a database if you wan
t to restore from a (that)
> snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you wan
t to revert to some
> certain snapshot for one of the users without affecting the other users (u
nless each user have their
> own database). How would SQL Server understand what data that belongs to u
ser A and revert only that
> data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...|||<DIV>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e7Mln5O0GHA.772@.TK2MSFTNGP05.phx.gbl...</DIV>> SQL Server has "database
snapshot" that you could potentially use. You can restore from a database[vbcol=seagreen]
> snapshot. But there can only exist *one* snapshot of a database if you
> want to restore from a (that) snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you
> want to revert to some certain snapshot for one of the users without
> affecting the other users (unless each user have their own database). How
> would SQL Server understand what data that belongs to user A and revert
> only that data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
A "marked transaction" can be your checkpoint. They are all stored in
msdb..logmarkhistory. You can restore a database backup and apply the
transaction log up through a marked transaction.
Using Marked Transactions (Full Recovery Model)
http://msdn2.microsoft.com/en-us/library/ms187014.aspx
David|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to dis
k.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty page
s
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:

Checkpoints

Hi,
i have a complex database. If a user (database is set to single user)
make changes, and the user want do redo these changes, i need a kind of
manual setted checkpoints. Such a checkpoint is set before changes
made.
The idea is, to set a checkpoint, (store all checkpoints), and when its
necassary you can revert to a checkpoint.
Is this possible or is there another technique to realize this?
Thank you in advance
AlexSQL Server has "database snapshot" that you could potentially use. You can restore from a database
snapshot. But there can only exist *one* snapshot of a database if you want to restore from a (that)
snapshot.
But perhaps it is much cleaner to begin a transaction and rollback?
There's no built-in scheme where many users has many snapshots and you want to revert to some
certain snapshot for one of the users without affecting the other users (unless each user have their
own database). How would SQL Server understand what data that belongs to user A and revert only that
data? Not to speak about relationships between tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>|||Every change which is made, is encapsulated by an transaction and in
errorcase undone by the rollback or if success with commit.
So i want to set a checkpoint at start of editing the tables, and if
the user want to
undo his changes (created new datarows etc.) so the user can go to the
checkpoint, created at start editing.
So it is a single user instance all changes since last checkpoint
should be reverted.
A case of "higher level transaction"
Alex
Tibor Karaszi schrieb:
> SQL Server has "database snapshot" that you could potentially use. You can restore from a database
> snapshot. But there can only exist *one* snapshot of a database if you want to restore from a (that)
> snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you want to revert to some
> certain snapshot for one of the users without affecting the other users (unless each user have their
> own database). How would SQL Server understand what data that belongs to user A and revert only that
> data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> > Hi,
> > i have a complex database. If a user (database is set to single user)
> > make changes, and the user want do redo these changes, i need a kind of
> >
> > manual setted checkpoints. Such a checkpoint is set before changes
> > made.
> >
> > The idea is, to set a checkpoint, (store all checkpoints), and when its
> > necassary you can revert to a checkpoint.
> > Is this possible or is there another technique to realize this?
> >
> > Thank you in advance
> > Alex
> >|||<DIV>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:e7Mln5O0GHA.772@.TK2MSFTNGP05.phx.gbl...</DIV>> SQL Server has "database
snapshot" that you could potentially use. You can restore from a database
> snapshot. But there can only exist *one* snapshot of a database if you
> want to restore from a (that) snapshot.
> But perhaps it is much cleaner to begin a transaction and rollback?
> There's no built-in scheme where many users has many snapshots and you
> want to revert to some certain snapshot for one of the users without
> affecting the other users (unless each user have their own database). How
> would SQL Server understand what data that belongs to user A and revert
> only that data? Not to speak about relationships between tables.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "alex" <earthquake.de@.freenet.de> wrote in message
> news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
>> Hi,
>> i have a complex database. If a user (database is set to single user)
>> make changes, and the user want do redo these changes, i need a kind of
>> manual setted checkpoints. Such a checkpoint is set before changes
>> made.
>> The idea is, to set a checkpoint, (store all checkpoints), and when its
>> necassary you can revert to a checkpoint.
>> Is this possible or is there another technique to realize this?
A "marked transaction" can be your checkpoint. They are all stored in
msdb..logmarkhistory. You can restore a database backup and apply the
transaction log up through a marked transaction.
Using Marked Transactions (Full Recovery Model)
http://msdn2.microsoft.com/en-us/library/ms187014.aspx
David|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to prevent folks from
wasting time on already answered questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup), and
indicate that you've already posted elsewhere.
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"alex" <earthquake.de@.freenet.de> wrote in message
news:1157460616.313916.180170@.d34g2000cwd.googlegroups.com...
> Hi,
> i have a complex database. If a user (database is set to single user)
> make changes, and the user want do redo these changes, i need a kind of
> manual setted checkpoints. Such a checkpoint is set before changes
> made.
> The idea is, to set a checkpoint, (store all checkpoints), and when its
> necassary you can revert to a checkpoint.
> Is this possible or is there another technique to realize this?
> Thank you in advance
> Alex
>

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:
> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> > Do I understand correctly that iIf I execute the CHECKPOINT statement from
> > query analyzer for a selected database, all uncommitted transactions in
> > the
> > transaction log are physically written to the database at that time?
> >
> > If so then do I need to manually truncate the log at another time to
> > reduce
> > it's size. Because I'm assuming the CHECKPOINT does not automatically do
> > that.
> >
> > Thanks for the clarification.
>
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA

Checkpoints

Do I understand correctly that iIf I execute the CHECKPOINT statement from
query analyzer for a selected database, all uncommitted transactions in the
transaction log are physically written to the database at that time?
If so then do I need to manually truncate the log at another time to reduce
it's size. Because I'm assuming the CHECKPOINT does not automatically do
that.
Thanks for the clarification.
No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
The "write-ahead log" (WAL) protocol used by SQL Server does not require
that all pages changed by a transaction are flushed to disk at the time of
the transaction commit; it only requires that the log records that affect
those transactions be persisted in the transaction log so that those
operations can be undone or redone in the case of a crash. The dirty pages
themselves can be written at the database system's lesiure. The number of
dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
interval) directly affects recovery time.
Books Online topic "CHECKPOINT" describes its function fairly well.
Thanks,
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
> Do I understand correctly that iIf I execute the CHECKPOINT statement from
> query analyzer for a selected database, all uncommitted transactions in
> the
> transaction log are physically written to the database at that time?
> If so then do I need to manually truncate the log at another time to
> reduce
> it's size. Because I'm assuming the CHECKPOINT does not automatically do
> that.
> Thanks for the clarification.
|||Ryan,
Thanks very much... you're explanation makes it very clear!
"Ryan Stonecipher [MSFT]" wrote:

> No, it is CHECKPOINT's job to flush "dirty" (or changed) data pages to disk.
> The "write-ahead log" (WAL) protocol used by SQL Server does not require
> that all pages changed by a transaction are flushed to disk at the time of
> the transaction commit; it only requires that the log records that affect
> those transactions be persisted in the transaction log so that those
> operations can be undone or redone in the case of a crash. The dirty pages
> themselves can be written at the database system's lesiure. The number of
> dirty pages in memory at teh time of a crash (and therefore the CHECKPOINT
> interval) directly affects recovery time.
> Books Online topic "CHECKPOINT" describes its function fairly well.
> Thanks,
> --
> Ryan Stonecipher
> Microsoft Sql Server Storage Engine, DBCC
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Naj Parandah" <NajParandah@.discussions.microsoft.com> wrote in message
> news:18484540-E4E0-48FB-A2F0-472231705649@.microsoft.com...
>
>

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
I believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote: