SQL Server Performance

Maint Plan Fails due to excessive locks on Repl DB

Discussion in 'Performance Tuning for SQL Server Replication' started by Randy, Jan 7, 2004.

  1. Randy New Member

    I have a configuration of single publisher, many subscribers, merge replication. I have a maintenance plan failing on backup that appears to be due to excessive locks on the db. I do not have integrity checks enabled. If the server runs for a while, the job fails. If I reboot, then immediately run the job it works. Does anyone have a suggestion for managing the locks or access to the db so the backup job will run successfully?

    Thanks
  2. Twan New Member

    What is the error for the job when it does fail?

    Cheers
    Twan
  3. satya Moderator

  4. Randy New Member

    No errors are ever returned because the maintenance plan never "fails" because the backup job never does a timeout, so nothing is ever recorded. I imagine if we set the timeout period to 2 minutes on the server for the backup timeout (default is wait forever), then we would see a message in the history log that the backup job timed out or some such error.

    When I was looking at the problem the other day, I didn't see any applicable errors in the event logs or sql logs that pointed to a specific failure of the backup.
  5. ChrisFretwell New Member

    If you are comfortable killing the processses unseen that have the exclusive locks, you can schedule a process that will find and kill non-system processes before beginning the maintenance plan.
    Otherwise, you should find out who/what has exclusive locks. Have a profiler session start a bit before your maintenance job. Save the results to a table and analyse them. You may just need to tweak some code, but until you do the analysis you may just be making stabs in the dark at a solution.

    Chris
  6. satya Moderator

    Each maint.plan activity will be recorded on the Maint.plan log generated under LOG directory of MSSQL, check it for details.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Randy New Member

    Found the problem via the process id's. There was another backup job running from another server with a tape drive locking the db due to the fact that the job didn't complete over there.

    thanks
  8. satya Moderator

    The best bet to tackle this is to backup the database to local drive and then use OS tape s/w to backup to the tape, this way you can reduce a lot stress on SQL server for the sake of tape backup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page