SQL Server Performance Forum – Threads Archive

Maint Plan Fails due to excessive locks on Repl DB

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

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

Schedule the integrity checks when there is less activity on the database and as referred check Maint plan history log for details of failure. Refer to this linkhttp://www.sql-server-performance.com/reducing_locks.asp&e=7415 to reduce the locks.http://www.sql-server-performance.com/q&a36.asp&e=7415 for Q&A information. 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.

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.

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

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.

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

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.

]]>