SQLLOGSHIP.EXE

  • Hello.

    We have a Reporting Server that relies on Transaction Log Shipping (currently but will change).
    The database is set to Standby/Read only and usually all is well when the jobs run to replay the logs.
    However the job to restore the logs failed with an error suggesting contention for DB Access.

     I think that I see what the issue is - a race condition - where something else jumps in to access the
    DB between logs.
    What I believe that I am seeing is :
    A command being generated when the SQLLOGSHIP.EXE Restore job kicks off that puts the DB in Single User mode
    Individual commands being submitted synchronously for each log file
    Another command putting the DB back into Multi User after all log file Restores/Deletes are complete
    I also see DB Online messages after each Log Restore.

    I  assume that all of this is managed by the EXE file - but I wasn't aware that the DB was put into Single User
    Mode or that the Log Restore process only had a temporary lock on the DB (not a lock for the full duration).

    My supposition is that something logged in to the database in the tiny interval between log file Restores, and 
    because the database is in Single User mode the attempt to connect to apply the next log file failed.
    Does that sound right to the folks in the know?

    If this is the problem, and given that no timeout values are specified on execution of SQLLOGSHIP.EXE, should
    we be coding something as a backstop (in addition to finding out what connected to the DB and moving it's
    execution)?

    What I did want to try was somehow utilising the NORECOVERY option on the DB to speed up the log file Restores
    - which would lock out other access anyway (as I understand it) but is problematic to automate in that it requires a
    log file Restore to run AFTER setting the DB back. I am also unsure from some internet posts as to whether I 
    can actually do this without restoring a DB Backup first.

    Steve O.

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply