MS SQL Server :: Unreliable Checkpoint

Unreliable Remote Connection(s)

Jan 13, 2008

I have setup a box as a SQL 2005 Express Server. It accepts remote connections and has the users/permissions setup. Both TCP/IP and Named Pipes are enabled.

When connecting to the server with MS SQL Server management Studio Express from another machine, on the same network, I have to try and login several times before it connects OK.

Can anyone suggest how to increase connection reliability please?

View 4 Replies View Related

Stored Procedures And Unreliable Results

Apr 21, 1999

Has anyone had a problem where the stored procedure does not return the same results as compared to running the same SQL in ISQL? This happens when rows are added to a table that's in a join and the SP doesn't return those rows. Once it starts happening, it will not work consistently, but it doesn't always happen when new rows are added to a table. It has happened to many stored procedures and with many different tables. sp_recompile won't fix the problem. Only dropping and re-adding the SP will fix the problem with that stored procedure. We are using SQL 6.5.

View 2 Replies View Related

CURSOR_STATUS Unreliable: SQL 2005 -- Is There A Better Way To Find Cursor Status?

May 27, 2008

What is the best way of reliably closing a cursor inside a BEGIN CATCH block? The simple problem statement is: I want to check if the cursor is open, then close it. I'm trying to use CURSOR_STATUS function and it seems to return a -3 (both when the cursor is open and not open.). Is this a bug or am I missing something?

I have removed all of my code and only provided what is necessary to repro the problem.

====================================================================================================

CREATE PROCEDURE dbo.repro_PROC

(

@condition1 varchar(10),

@condition2 varchar(10)

)

AS

BEGIN

BEGIN TRY

SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @dbName varchar(64), @dbid int;

DECLARE @FileID int;

DECLARE @Physical_Name varchar(512);

IF (@condition1 = 'ERROR')

RAISERROR ('ERROR: This error happens before any cursor is declared or is open', 16, 1);

DECLARE dbNameCursor CURSOR FOR SELECT Name, database_id FROM master.sys.databases ORDER BY Name;

OPEN dbNameCursor;

FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE fileCursor CURSOR FOR SELECT File_ID, Physical_Name FROM master.sys.master_files

WHERE database_id = @dbid

ORDER BY File_ID;

OPEN fileCursor;

FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

WHILE @@FETCH_STATUS = 0

BEGIN

-- some condition which causes an exception

IF (@condition2 = 'ERROR')

RAISERROR ('ERROR: This error happens after both cursors are open', 16, 1);

FETCH NEXT FROM fileCursor INTO @FileID, @Physical_Name;

END

CLOSE fileCursor;

DEALLOCATE fileCursor;

FETCH NEXT FROM dbNameCursor INTO @dbName, @dbid;

END

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

DECLARE @fileCursorStatus INT;

DECLARE @dbNameCursorStatus INT;

SELECT

@ErrorMessage = ERROR_MESSAGE(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE();

SET @fileCursorStatus = CURSOR_STATUS('local', 'fileCursor');

SET @dbNameCursorStatus = CURSOR_STATUS('local', 'dbNameCursor');

PRINT 'Cursor_Status (fileCursor) = ' + convert(varchar, @fileCursorStatus);

PRINT 'Cursor_Status (dbNameCursor) = ' + convert(varchar, @dbNameCursorStatus);

IF (@fileCursorStatus >= 0)

BEGIN

PRINT 'Closing fileCursor';

CLOSE fileCursor;

DEALLOCATE fileCursor;

END

IF (@dbNameCursorStatus >= 0)

BEGIN

PRINT 'Closing dbNameCursor';

CLOSE dbNameCursor;

DEALLOCATE dbNameCursor;

END

-- info about the original error

RAISERROR (@ErrorMessage, -- Message text.

@ErrorSeverity, -- Severity.

@ErrorState -- State.

);

END CATCH;

END;

====================================================================================================

EXEC dbo.repro_PROC @condition1='ERROR', @condition2='NO-ERROR'

Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 88

ERROR: This error happens before any cursor is declared or is open

-------------------------------------------------------------------------

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'

Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

ERROR: This error happens after both cursors are open

-------------------------------------------------------------------------

EXEC dbo.repro_PROC @condition1='NO-ERROR', @condition2='ERROR'

Output:

Cursor_Status (fileCursor) = -3

Cursor_Status (dbNameCursor) = -3

Msg 50000, Level 16, State 1, Procedure repro_PROC, Line 89

A cursor with the name 'dbNameCursor' already exists. <=== THIS IS A PROBLEM

=================================================================================================

If I try to close the cursor when the status = -3, then sometimes it closes successfully, some other times, it errors "Cursor NOT open" etc.

View 6 Replies View Related

Designing SSB Queues, EA Sample And Unreliable Interdependant NTFS Tasks

Feb 21, 2006

Our current project involved into managing NTFS hierarchical folders structured by multi-level customers and associated projects for them. Essentially each folder level represents one level of customer hierarchy or project root. Both have different subfolders and user access rights for them based on generic XML templates. The folders resided on file servers across the country and should be accessible in ordinary way NTFS file shares are allowing. LAN/Intranet MS AD Win2003 / SQL2005 environment.
The folder management system basically have to keep the folder structure in tact with changes in underlying managing application logic. That involves such operations as creating a new folder with subfolders, copying a folder with its content into another folder branch (which may be on the same or another server and place), deleting a folder/content, renaming a folder, applying NTFS access rights to folders and subfolders for users according generic templates. As all these actions are unreliable and some tasks may take hours to complete, SSB approach seems to be the viable solution. Some tasks involved have to be done within 10 minutes, others (are prolonged) have to be scheduled for overdnight run. Windows service like EA sample may be used to start the actual NTFS related tasks. Content transfer involves WMI remoting with robocopy tool on target machine (for better network utilization), other related tasks make use of WMI APIs and probably do direct (i.e. synchronous) calls to the remote target file server.
At this stage, making atomary executable modules that do just one functionally isolated task like DirCopy, DirCreator, DirRename, DirDelete, DirUserAccess seems like somewhat logical choice.
The questions starting to arise from SSB queues planning to adoption of ExternalActivator sample to run these atomic executables. The problem is that if SSB messages contain atomic tasks for these executables, they needed to be syncronized in two ways - by execution precedence (create or copy dir first, then apply users' access rights) and transactionally (only when all related tasks succeed, the appropriate feedback action and event log writing can be taken.

I can imagine two implementation scenarios below.
Case A. Create common queue for directory creating, content transfer and access rights.
In that case ExternalActivator has to be either extended & re-designed or it has to activate another Activator-Coordinator (middleway) executable, that would actually read the queue and, based upon the message type, run the appropriate atomary executable. In that scenario, the queue processing would be stalled because if the previous task was content transfer and lasts, say access rights to them can't be started before the transfer task has been finished OK. That in turn probably will require running multiple instances of the atomary tasks and using conversation groups, wouldn't it? What would be the most reliable and simple way to achive that?

Case B. Dedicated queues for each atomary executable modules.
Neither EA changes nor extra Activator-Coordinator middleway executable required. But because the atomary task-oriented queues are not syncronized with each other the queues internal conversation groups wouldn't help much... That means if a directory not yet exists, userRights module have to wait. But, what if we are transferring directories from path X to Y, based on what the userRights module knows to wait? With zero asynchronous design experience I'm lost here...
Hope I described the app domain understandably, thanks for hints leading to the working solution!

View 8 Replies View Related

Checkpoint

Mar 5, 2001

Can i force a checkpoint?How?Will it have any implication?
TIA

View 3 Replies View Related

Checkpoint

Aug 26, 2003

Please 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

View 3 Replies View Related

Checkpoint Not Being Issued

Oct 4, 2005

Hi,

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.

View 1 Replies View Related

Checkpoint Process

Apr 12, 2007

I 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

View 4 Replies View Related

Checkpoint Restart

Jan 24, 2006

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

View 10 Replies View Related

Checkpoint Not Being Set? Log Not Truncating?

Oct 26, 2007

Hi,

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

View 2 Replies View Related

Transaction And Checkpoint

Dec 7, 2007

If 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?

View 6 Replies View Related

Truncate On Checkpoint And Autoshrink?

May 9, 2002

Details: 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

View 2 Replies View Related

Truncate Log On Checkpoint Keeps Checking Itself.

Nov 15, 2000

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

View 1 Replies View Related

Truncate On Checkpoint Not Truncating

Dec 30, 1998

The 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

View 2 Replies View Related

CHECKPOINT Backgroup Process

Apr 16, 2008

Hello,

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

View 4 Replies View Related

Server Freezes While Doing Checkpoint ?!

Jul 24, 2007

We'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

View 6 Replies View Related

Checkpoint Command And Backup Log

Jul 20, 2005

This 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 Related

Error When Trying To Use Checkpoint File

Jun 20, 2006

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

View 3 Replies View Related

How Do I Create A Checkpoint File?

Oct 24, 2006

hi everyone,

Does anyone ever used or created something like that? What kind of format it might be and so on...

TIA

View 8 Replies View Related

Is 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

View 7 Replies View Related

Checkpoint With Parallel Packages

May 13, 2008

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

View 1 Replies View Related

Checkpoint File Used By Another Process

Dec 5, 2006

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

View 5 Replies View Related

Database Options/truncate Log On Checkpoint

Nov 27, 2000

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

System Databases & Truncate Log On Checkpoint

Jul 7, 1999

I 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

View 1 Replies View Related

General Question Of Before Image Vs Checkpoint

Jul 23, 2005

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

Is It Possible To Execute A Container Regardless Of The Checkpoint File?

Feb 23, 2006

I 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

View 17 Replies View Related

Bug: 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]

View 3 Replies View Related

Understanding The Contents Of A Checkpoint File

Aug 18, 2006

Hi,

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

View 1 Replies View Related

No Of Package Runs Using Checkpoint File

Nov 30, 2006

hi 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,

View 3 Replies View Related

New Guy Question: Which Came First ... The Chicken Or The Checkpoint File?

Oct 18, 2005

Hello all...here's a question from a newbie ...

View 8 Replies View Related

Dynamically Setting A Checkpoint Filename

Mar 14, 2008

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

View 4 Replies View Related

SQL Server 2012 :: Transaction Log Not Clearing At Checkpoint

Aug 14, 2015

I have a Database that is set to Simple Recovery mode.

It is not clearing the log and the transaction log is growing.

I thought that it would clear the log upon checkpoint?

How can I force the Transaction Log to clear since NO-TRUNCATE is no longer available?

View 9 Replies View Related