Skip to Content
author's profile photo Former Member
Former Member

database dump, update stats, reorg, dbcc blocks

if a dump database or dump tran is running on a database, will it block other processes from executing on the db?

likewise with update stats, reorg, dbcc - in execution will they block other processes?

even within themselves - will a dump block a reorg or dbcc?

I have seen instances of this happening now and again, but would like to know more theory/reasons on this issue.

appreciate the feedback.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Jan 09, 2014 at 12:19 AM

    The dump command does not block activity in the database, though it could degrade performance if your disk subsystem and/or cpu are currently running at/near max capacity.

    The reorg and dbcc commands may or may not block user activity ... it depends on which of the reorg and dbcc commands you're running. It will also depend on your ASE version (eg, SP100 has new capabilities that allow some older blocking-type commands to run without blocking).

    As for having seen blocks in the past ... we'll need a bit more detail (eg, actual/complete command that was doing the blocking, what commands/user-activity was being blocked) if you're looking for explanations.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 09, 2014 at 12:47 AM

    Expanding a little on Mark's answer

    Dump commands do block other dump commands on the same database. This is done to ensure that the resulting files can be loaded in sequence.

    There is a very short phase in a full database dump where processes that

    are doing unlogged updates get put to sleep until the phase completes, which

    is kind of like blocking, though it isn't obvious.

    The QUIESCE DATABASE command will block processes that try to write to the device, but readers are not blocked, at least not until the cache fills completely with dirty buffers modified by other processes (a dirty buffer has to be written out before new data can be read into the buffer).

    Until recently, REORG REBUILD always took an exclusive table lock and held it until the command completed. The other REORG options would lock a handful of pages at a time, so there was blocking, but it was short lived. The new online option greatly improve the concurrency of REBUILD.

    In general, you can use traceflag 1212 (see ASE Traceflag 1212 - Enterprise Information Management - SCN Wiki) on a quiet test system to trace what locks are aquired and released by individual commands. The type of locks and duration they are held determine what it will block. Note that there can be surprising amounts of output, which is why I recommend only using it on a system where there is no other activity.


    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2014 at 03:06 AM

    mark/bret, thanks for the response.

    sorry for not being more specific.

    maybe i should have asked : what kind of lock(s) does dbcc checkstorage, dbcc checkverify, update (index) statistics put on a database/table.

    where i work i remember a couple of instances where a database dump was being blocked by update stats or reorg. or it was the other way round. i don't remember exactly how it was.

    bret, thanks for that pointer about how reorg rebuild works in new versions. will look it up. for the options - forwarded rows and reclaim space - are exclusive locks held on the relevant pages?

    appreciate the feedback.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.