New Guy Question: Which Came First ... The Chicken Or The Checkpoint File?
Oct 18, 2005Hello all...here's a question from a newbie ...
View 8 Replies
ADVERTISEMENT
Error When Trying To Use Checkpoint File
Jun 20, 2006I have a package with settings SaveCheckpoints=True and CheckpointUsage = Never. After the package failed I fixed the cause of failure by setting a database column to allow nulls. Then I went to our web app that we built to monitor package execution and clicked on the button to restart the package. The web app loads the package and then sets the CheckpointUsage property of the package object to 'Always'. Then it executes the package in a new thread. The package then produced this error:
Checkpoint file "E:Package1Checkpoint" failed to open due to error 0x80070005 "Access is denied.".
Since there was only one remaining task to run in the package I ran it manually.
Now here is the really interesting part. I then needed to run the same package but with different parameters. When I attempted to run it with the saved checkpoint settings (CheckpointUsage=Never, SaveCheckpoints=True) I got this error:
"An existing checkpoint file is found with contents that do not appear to be for this package, so the file cannot be overwritten to start saving new checkpoints. Remove the existing checkpoint file and try again. This error occurs when a checkpoint file exists, the package is set to not use a checkpoint file, but to save checkpoints. The existing checkpoint file will not be overwritten. "
So I then attempted to rename the checkpoint file so it would not interfere, however, it would not let me, saying that the file was in use.
So what I had to do was add a configuration entry which set SaveCheckpoints to False. Then I was able to run the package.
Any ideas?
How Do I Create A Checkpoint File?
Oct 24, 2006hi everyone,
Does anyone ever used or created something like that? What kind of format it might be and so on...
TIA
Checkpoint File Used By Another Process
Dec 5, 2006I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.
One out of two runs will fail with one of the below errors:
The checkpoint file \xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.
Checkpoint file \xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces
I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.
This is causing the entire job to fail even though the control flow was successful.
Anyone encounter this issue? Any assistance is appreciated.
Thanks!
Is It Possible To Execute A Container Regardless Of The Checkpoint File?
Feb 23, 2006I have a situation where I need to make sure a task executes regardless of whether the package starts from a checkpoint or not. Is this possible?
Here's the scenario:
I have a package with 3 tasks {TaskA, TaskB, TaskC} that execute serially using OnSuccess precedence constraints.
The package is setup to use checkpoints so that if a task fails the package will restart from that failed task
TaskA is insignificant here.
TaskB fetches some data and puts it in a raw file
TaskC inserts that raw file data into a table.
Problem is that the insertion violates an integrity constraint in the database - so it fails. The problem is easily fixed but it needs to be fixed in TaskB because that is where the data is sourced.
So, I need to be able to execute TaskB again, even though it was TaskC that failed. Currently the package restarts from TaskC which reuses the raw file (which has the bad data in it) so the package continues to fail even though the cause of the problem has been fixed.
How do I configure the package in order to execute TaskB again? Is it even possible?
Regards
-Jamie
Understanding The Contents Of A Checkpoint File
Aug 18, 2006Hi,
I have a problem where my packahe isn't restarting from the point of failure and I'm tryig to understand why not.
The following line came from the checkpoint file:
<DTS:Container DTS:ContID="{36A685E1-049A-4E6C-9884-AADF8BE29577}" DTS:Result="0" DTS:PrecedenceMap="" />
Can anyone explain:
What DTS:Result & DTS:PrecedenceMap are used for?
What values can DTS:Result contain and what do they mean? [Today I have only seen "0".]
What values can DTS:PrecedenceMap contain and what do they mean? [Today I have only seen "Y" & "".]
Thanks
Jamie
No Of Package Runs Using Checkpoint File
Nov 30, 2006hi everyone,
Is there anyway to find out the number of runs of a package using its checkpoint file?
Or is there anyother way to figure out the number of runs of a package till it gets succeeded?
Thanks in advance,
Execute A Task Even Though A Checkpoint File Says It Has Already Been Executed
Dec 16, 2005I have the following scenario:
-I have a package that uses checkpoints
-The first task in that package (an Exec SQL Task) retrieves a timestamp value from an external source
-That timestamp value is used in data-flows to pull data that has been changed since then
-At the end of the package the value in the db is updated
Now...if something fails then on the next execution the checkpoint file will kick in. But this means that the first task (which retrieves the timestamp) will not execute and therefore all the data-flows will be pulling the wrong data.
The only way I can think of getting around this problem is to specify that a task should execute regardless of the presence of a checkpoint file. Unfortunately it seems this cannot be done.
Another option might be to put an OnPreExecute task on the package that gets the timestamp value.
Anyone got any advice about how I should progress? Short of a suggestion from elsewhere I'm going to go with the tactic of using the OnPreExecute to retrieve the timestamp.
Thanks in advance.
-Jamie
Checkpoint File Falied During Creation - How To Figure This Error Out
Oct 12, 2007hi folks,
i have this error when trying to run the package in development mode.
and i can not decipher this non-intuitive message:
Error: 0xC001604D at <package name>: Checkpoint file "" failed during creation due to error 0x80070003 "The system cannot find the path specified.".
please note that i have no checkpoints on package anymore; i had two but i deleted them but this message still appears.
i have searched the web but no luck in finding an answer.
maybe someone would be able to point out my obvious mistake here.
many thanks,
Nicolas
Integration Services :: Create SSIS Package Checkpoint-file Property With UNC Path
Aug 26, 2015I can set the propperty of the checkpoint file to a local drive, but not to a UNC path mapping, mapping to my host server. (loop back)
Example: "I:FILEFILE1$InputArchiveOntwikkel " is possible as checkpoint file property.
S11487O$InputArchiveOntwikkel is not possible, though this is the same folder on the local host.
For data source both unc path and drive mapping are allowed. Why this difference?
Deadlock And High Cpu - Chicken Or The Egg
Dec 12, 2007I was asked to look into a performance problem on a newly migrated DBserver.The db server was moved from a local-physical-nt4-sybase to remote (10mb wan link), virtual, Windows 2003, SQL 2005.The client side application had to be modified to work with MS SQL.This is all second hand information as I have just been thrown intothis. Most of the people who set this up ran.The 20 clients do some data entry all day which culminates into all 20stations running an end of day procedure at the same time. Thisparticular event creates 3 things :- very high and constant CPU usage on the SQL server- deadlock victim errors on some of the clients- very slow "end of day" performance.This use to work flawleessly on the former setup.My question is about deadlocks. Can they be generated by the high CPUusage/ slow response or can they be the actual source of the CPUpeak ?I suspect I might be in front of multiple problems:- underpowered vm (i have asked to increase Ram and cpu cycles to thevm which will take a few days)- badly tuned sql applicationI'm not asking for a solution to this, just some conventional wizdomon deadlock and high cpu.Thanks in advance.
View 8 Replies View RelatedChicken And Egg Problem: FOREIGN KEY Reference To A Table That Doesn't Exist Yet
Jul 23, 2005I'm trying to create a local copy of a popular CRM database calledSalesforce.com. Many of the tables in the DB have FOREIGN KEYreferences that I want to preserve, but I've run into a chicken and eggproblem. Table "A" has a reference to table "B," and table "B" has areference to table "A." So I can't CREATE one until the other exists.Is there a way to disable these checks until I've created all theschema?Here's what I see (error first, then SQL that caused it):Server: Msg 1767, Level 16, State 1, Line 1Foreign key 'FK__UserRole__LastMo__48CFD27E' references invalid table'User'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors.CREATE TABLE salesforce3.dbo."UserRole" ("Id" varchar(18) PRIMARY KEY ,"Name" varchar(40), "ParentRoleId" varchar(18) REFERENCES"UserRole"(Id), "RollupDescription" varchar(80),"OpportunityAccessForAccountOwner" varchar(40),"CaseAccessForAccountOwner" varchar(40), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime);CREATE TABLE salesforce3.dbo."User" ("Id" varchar(18) PRIMARY KEY ,"Username" varchar(80), "LastName" varchar(80), "FirstName"varchar(40), "CompanyName" varchar(80), "Division" varchar(80),"Department" varchar(80), "Title" varchar(80), "Street" text, "City"varchar(40), "State" varchar(20), "PostalCode" varchar(20), "Country"varchar(40), "Email" varchar(80), "Phone" varchar(40), "Fax"varchar(40), "MobilePhone" varchar(40), "Alias" varchar(8), "IsActive"bit, "TimeZoneSidKey" varchar(40), "UserRoleId" varchar(18) REFERENCES"UserRole"(Id), "LocaleSidKey" varchar(40), "ReceivesInfoEmails" bit,"ReceivesAdminInfoEmails" bit, "EmailEncodingKey" varchar(40),"ProfileId" varchar(18) REFERENCES "Profile"(Id), "LanguageLocaleKey"varchar(40), "EmployeeNumber" varchar(20), "WirelessEmail" varchar(80),"LastLoginDate" datetime, "CreatedDate" datetime, "CreatedById"varchar(18) REFERENCES "User"(Id), "LastModifiedDate" datetime,"LastModifiedById" varchar(18) REFERENCES "User"(Id), "SystemModstamp"datetime, "UserPermissionsMarketingUser" bit,"UserPermissionsOfflineUser" bit, "UserPermissionsWirelessUser" bit,"UserPermissionsSuperCssUser" bit, "UserPermissionsAvantgoUser" bit);
View 2 Replies View RelatedCheckpoint
Mar 5, 2001Can i force a checkpoint?How?Will it have any implication?
TIA
Checkpoint
Aug 26, 2003Please help.
I would like to checkpoint my transaction log every night before full backup.
Would this affect the transaction log sequence in the event of a restore.
I run SQL Server 2K SP 3 on WIN 2K SP 3.
Thank you.
Regards
Checkpoint Not Being Issued
Oct 4, 2005Hi,
Has anybody encountered this situation before? DB on SQL Server 2000 SP4 with trunc log on chkpt option turned on. Checkpoint trace flags were turned on but noticing no checkpoints are being done on one specific DB resulting into growing transaction log. No open transactions.
Any ideas?
Thanks.
Checkpoint Process
Apr 12, 2007I see a line in sys.sysprocesses. The process's status is suspend and the command is CHECKPOINT. I have the information here exactly as it is on my monitor. It seems is consuming hi cpu. What should I do?
[/code]
spid: 10
kpid: 7416
block: 0
waittype: 0x0081
waittime: 232546
lastwaittype: CHECKPOINT_QUEUE
waitresource:
dbid: 1
uid: 1
cpu: 427046
physical_io: 36695
memusage: 0
login_time: 2007-04-04 10:01:32.787
lastbatch: 2007-04-04 10:01:32.787
ecid: 0
open_tran: 0
status: suspended
sid:0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
hostname:
program_name:
hostprocess:
cmd:CHECKPOINT nt_domain:
nt_username:
loginame: sa
[/code]
Canada DBA
Checkpoint Restart
Jan 24, 2006I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
Unreliable Checkpoint
Nov 27, 2006With 2005 SP1. Have built a SSIS package that successfully saves a checkpoint file and sometimes successfully restarts. (I've also built some others that are 100% reliable).
On the unsuccessful restart it appears as though the failed steps and subsequent steps do not execute. the package appears to "complete" though and the checpoint file is removed as though everything is fine.
On a successful restart the failed step reexcutes and everything works fine.
The issue appears that when a failed step finishes at the same time as a successful step finishes there is contention in the process that writes the checkpoint file out and the checkpoint file is corrupt. The failing step runs in parallel with a successful step and the execution times are very similar so task A may complete before or after task B.
Contents of a good checkpoint file follows <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
Contents of a bad checkpoint file follows
<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{9FAD4043-8D5F-4044-915A-87ECABDE6A7C}" DTS:Result="1" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
Has anyone seen this behaviour before?
Checkpoint Not Being Set? Log Not Truncating?
Oct 26, 2007Hi,
I've set up a number of jobs (not a maintenance plan) via a script in SQL 2005. These jobs do the following:
1) Full backup every sunday night
2) Differential backup every weeknight
3) Log backup every hour
The database is obviously in the full recovery model.
The backups all seem to be running, with one issue - the log file is still growing and is not being truncated. I was under the impression that a log backup should result in the log being truncated after each full backup. However, this does not seem to be the case.
Is there anything obvious I've missed that needs to be set up, or is there a way I can check that the full backup is actually setting the appropriate checkpoint and that the log backups are 'seeing' these checkpoints?
Thanks
Transaction And Checkpoint
Dec 7, 2007If i have 3 Tasks in my control flow with checkpoint enabled and the transactionoption of the tasks is required,
Transaction option of the package is supported.
if the second task fails , the package restart from the first task when its running again instead of using the checkpoint and begin from the second task
Can anyone tell me if it€™s a bug in SSIS?
Truncate On Checkpoint And Autoshrink?
May 9, 2002Details: MSDE 1.0 / SP4 - Windows 2000 Pro
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
TIA -RC
Truncate Log On Checkpoint Keeps Checking Itself.
Nov 15, 2000Can anyone assist with this problem.
Every now and then my overnight backups (backup Exec) fail due to the truncate log on checkpoint being enabled. This occasionally occurs on Master MSDB databases. I have unchecked the truncate log on checkpoint box numerous times and the backups work fine. Then mysteriously the box is checked again and the backups fail once more. I am stuck as to why this can happen. Is there a generic stored proceedure that checks this box ?
Help with this will be welcomed.
Truncate On Checkpoint Not Truncating
Dec 30, 1998The log on one of my databases keeps filling up, even though I have it set to truncate on checkpoint. the only real difference between this database and the others on my server is that it is built from the dump of another database (on another server) where the tables are marked for replication.
I'm wondering if the fact it is built from a replicating database could be causing this. I've noticed I can't drop any of the table, even though my database isn't set to replicate (or publish).
two questions
1) Any ideas?
2) Is there anyway I can make my server realize I'm not replicating so it will let me drop those tables? (nothing in Enterprise manager indicates that my database is replicating or publishing).
Thanks,
Jim
CHECKPOINT Backgroup Process
Apr 16, 2008Hello,
I am DB Developer (not admin), excuse me if this is a silly question.
I don't know much about CHECKPOINT background But I feel, this process is slowing down performance of my sps which runs slower than normal in some cases.
Especially when I see any of my process goes in the suspended mode and its wait type is SLEEP_BPOOL_FLUSH and CHECKPOINTs process is also suspended and its wait type is CHECKPOINT_QUEUE.
More important anything else is... this background process (which I always find its spid is 11) BLOCKS all other user processes when it goes into suspended mode and its wait type is SLEEP_BPOOL_FLUSH
I dont know my analysis is correct (claiming checkpoint as culprit), need experts advice and help
can someone give info on checkpoint and how this effect server performance
Server Freezes While Doing Checkpoint ?!
Jul 24, 2007We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.
We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.
Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.
Thanks
Checkpoint Command And Backup Log
Jul 20, 2005This is maybe a dumb question but I couldn't find a definitive answeron BOL.Looking at my backup script. If I issue a CHECKPOINT, does this trulyforce all transaction log entries to the data file? Therefore, makingit unnecessary to BACKUP log (just BACKUP database is needed).Louis
View 1 Replies View RelatedIs There A Checkpoint At Record Or Row Level
Feb 1, 2008
Hi All,
Is there a checkpoint at record or row level? i mean let say i have 2 million rows and i want to transfer from one db to another, when i start tranfering about 1 million records the package fails, thus next time i run i want to continue from where it was, i.e. since 1 million records were transfered i don't want to repeat them, just like checkpoint at Task Level. Farther information I am working on SSIS.
Thank you,
Sami
Checkpoint With Parallel Packages
May 13, 2008I have several packages within secuence containers and into one main dtsx package with a checkpoint configuration and when I run it some succeed and some don´t. The problem is that when I rerun it checkpoint doesn´t seem to work ´cause some of the successful packages are rerun as well (and not skipped as it should be...) In other words, the process does not begin on the point of failure..
Seems to be that packages that finish after the failure point (and succeed) are not registered in the checkpoint file, then when I rerun the main package these succeeded packages are rerun too....
Any ideas??
Database Options/truncate Log On Checkpoint
Nov 27, 2000I'm trying to make sure my database options are set up properly. Our database is used for our Decision Support System. The data is loaded in once a day. Should I have the truncate log on checkpoint on and should I limit the size that the log grows to. I just had to shrink the log. It grew so much that it took up all the space left. If I have the truncate log on checkpoint do I just have to issue the checkpoint command in order for it to truncate the transaction log?
View 1 Replies View RelatedSystem Databases & Truncate Log On Checkpoint
Jul 7, 1999I am new to SQL Server 7 and have inherited a server built by a consultant that is no longer here. I have noticed that the system databases (master, msdb & model) are completed backed up on a nightly basis and are all set with truncate log on checkpoint. Is this the proper way to have things set up?
Thanks.
Mike
General Question Of Before Image Vs Checkpoint
Jul 23, 2005I read recently that: "if a before-image was made before aninterruption in database processing, you use the before-image dump &simply begin processing again from just before the last transaction.Any checkpoints are irrelevant."I'm not a dba here, so bear with me... What I don't understand is, whyare checkpoints irrelevant when you did a before image dump? Why notbegin from the last checkpoint? Probably part of my problem isunderstanding the difference between the before image dump and acheckpoint. I know that the checkpoint is a db even that suspendsprocessing & writes data to the disk. It represents a moment in timewhen everything is OK in the db. That sounds a lot like a before imagedump :) Maybe the checkpoint is further back in time (?)Basically, if there's an interruption and you need to restore, do youresume from the transaction just prior to the last one, from the beforeimage? Or do you resume from the transaction from the checkpoint...thanks for any help -- jason shohet
View 1 Replies View RelatedBug: Sequence Containers And Checkpoint Files
Mar 3, 2008
There is a bug in SSIS2005 concerning the way that checkpoint files behave in concert with Sequence containers. It is documented (at length) here:
Is it possible to execute a container regardless of the checkpoint file?
(http://forums.microsoft.com/msdn/showpost.aspx?postid=1574262&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0)
Is anyone from Microsoft yet able to give a definitive answer to whether this will be fixed or not in Katmai? A yes or no answer would be very very much appreciated.
Regards
Jamie
[Microsoft follow-up]
Dynamically Setting A Checkpoint Filename
Mar 14, 2008I am trying to dynamically set the name of a checkpoint file and I have used expressions in the package property and evaluated the following expression correctly
Expression: "C:\CheckPoints\" + "CheckPointFile_ "+ @[User:BName]" + ".xml"
Evaluates as: C:CheckpointsCheckPointFile_@[User:BName].xml
when I try to save the package, it comes up with an error saying
Error at Caller Package: the filename is not valid. the filename is a device or contains invalid characters.
Anyone know why?