SQL Anywhere database - How do I tell if a CHECKPOINT is blocked by a backup?

Author

How do I tell if a CHECKPOINT is blocked by a backup?

Breck Carter [TeamSybase]

2005-06-20, 1:23 pm

How do I tell if a connection is blocked because it has issued an
explicit CHECKPOINT while a backup is running?

It doesn't seem to show up in sa_conn_info.BlockedOn, and setting
BLOCKING = 'OFF' doesn't have any effect:

SET TEMPORARY OPTION BLOCKING = 'OFF';
CHECKPOINT;

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Nick Elson

2005-06-21, 7:24 am

First off you would need to have the follow 2 precursor requirements
for checkpoints to be held up due to a backup::

You are doing a backup that includes the man dbspaces.

The dbspace backup must still be operating.

Checkpoints are only held up during the backup of the dbspace.
They are not held up during transaction log backups.

So the question reduces to this one:

Are you backing up a dbspace file (and not the TRANSLOG)?

if you have codified your own backup events you may be able
have enough control to 'know' the answer to this.

HTH

P.S. and to play the devil's advocate 'reorganize table' can also
hold up checkpoints and interact with the backup because of that.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:492eb1tblulbkh0
78jntucjef6rhaajtij@
4ax.com...
> How do I tell if a connection is blocked because it has issued an
> explicit CHECKPOINT while a backup is running?
>
> It doesn't seem to show up in sa_conn_info.BlockedOn, and setting
> BLOCKING = 'OFF' doesn't have any effect:
>
> SET TEMPORARY OPTION BLOCKING = 'OFF';
> CHECKPOINT;
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-06-21, 9:23 am

On 21 Jun 2005 05:51:29 -0700, "Nick Elson"
< no_spam_nicelson@syb
ase.com> wrote:

>First off you would need to have the follow 2 precursor requirements
>for checkpoints to be held up due to a backup::
>
> You are doing a backup that includes the man dbspaces.
>
> The dbspace backup must still be operating.
>
>Checkpoints are only held up during the backup of the dbspace.
>They are not held up during transaction log backups.
>
>So the question reduces to this one:
>
> Are you backing up a dbspace file (and not the TRANSLOG)?

Yes. It is a 25G database that has a full backup made once per day.
The backup takes a long time (more than an hour, I think). It is
usually run at 1AM but stuff happens :)

>
>if you have codified your own backup events you may be able
>have enough control to 'know' the answer to this.
>
>HTH

Not exactly. Are you saying there is no way to determine that an
explicit CHECKPOINT is blocked by a backup? (if so, I know where my
next posting's gonna be :)

>
>P.S. and to play the devil's advocate 'reorganize table' can also
>hold up checkpoints and interact with the backup because of that.

Do you know if these blocks show up in sa_locks and/or sa_conn_info or
are they the same thing as backup blocks? I haven't looked.

Breck

>
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:492eb1tblulbkh0
78jntucjef6rhaajtij@
4ax.com...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Nick Elson

2005-06-21, 8:23 pm

I ignored the blocking aspect of your original posting because
the questions asked are tied to record locking and not checkpointing.
At most checkpoint is a resource block and that would not show
up that way.

As to when checkpoints occur surrounding the backup a quick
review of

ASA Database Administration Guide
Backup and Data Recovery
Backup and recovery internals
Backup internals

should clarify my earlier points. Specifically:

1 - Issue a checkpoint. Further checkpoints are disallowed until the
backup
is complete. While the backup is taking place, any pages modified
by
other connections are saved before modification in the temporary
file,
instead of the database file, so that the backup image is made as
of the
checkpoint.

so checkpoints are held up for the duration of the main db file.
Checkpoints
are free to occur anytime during the transaction log backup [which occurs
last].

And, No, I know of no way of detecting this through some property. I guess
you could try to issuing your own checkpoint (possibly on another
connection)
to see if/when that returns; that or see if the database backup target file
is still
growing.

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:1v7gb1lf3ig15ld
f31cjndqj4efg15arc5@
4ax.com...
> On 21 Jun 2005 05:51:29 -0700, "Nick Elson"
> < no_spam_nicelson@syb
ase.com> wrote:
>
>
> Yes. It is a 25G database that has a full backup made once per day.
> The backup takes a long time (more than an hour, I think). It is
> usually run at 1AM but stuff happens :)
>
>
> Not exactly. Are you saying there is no way to determine that an
> explicit CHECKPOINT is blocked by a backup? (if so, I know where my
> next posting's gonna be :)
>
>
> Do you know if these blocks show up in sa_locks and/or sa_conn_info or
> are they the same thing as backup blocks? I haven't looked.
>
> Breck
>
>
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-06-21, 8:23 pm

On 21 Jun 2005 12:40:34 -0700, "Nick Elson"
< no_spam_nicelson@syb
ase.com> wrote:
>And, No, I know of no way of detecting this through some property. I guess
>you could try to issuing your own checkpoint (possibly on another
>connection)
>to see if/when that returns; that or see if the database backup target file
>is still
>growing.

Thanks. That is actually the issue... a connection is stuck, not
proceeding forward, and from an administrative point of view there is
no way to determine why it is stuck.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Nick Elson

2005-06-23, 11:23 am

.... and LastStatement didn't help there?

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:d1tgb193kleho1m
cdp38v4qnjck4goppdb@
4ax.com...
> On 21 Jun 2005 12:40:34 -0700, "Nick Elson"
> < no_spam_nicelson@syb
ase.com> wrote:
>
> Thanks. That is actually the issue... a connection is stuck, not
> proceeding forward, and from an administrative point of view there is
> no way to determine why it is stuck.
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-06-23, 11:23 am

On 23 Jun 2005 08:02:16 -0700, "Nick Elson"
< no_spam_nicelson@syb
ase.com> wrote:

>... and LastStatement didn't help there?

I can't remember what it said, but even if it says CHECKPOINT, that
doesn't actually tell you whether the statement is running or stuck...
and I want to know *why* it might be stuck.

Is it an unreasonable request?

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Sponsored Links