DB Mtce Plan Hanging on Backups | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DB Mtce Plan Hanging on Backups

SQL 7 sp 4 on Win 2K sp 4 I currently have 6 user dbs included in a db mtce plan. For four years the plan has run without any problem. Within the past week, 2 of the 6 databases fail to complete both db and transaction log backup. The mtce plan does not fail. The jobs just never finish. I can run backups for the dbs and transaction logs manually without a problem. They take only a few seconds to complete. I’ve tried creating a new, separate mtce plan for each db, without any optimizations, integrity checks or shrinks. These new mtce plans also fail to complete, no errors logged. If I restore the dbs to a different SQL server, then run an identically configured mtce plan on that server, it works. Does anyone know why a db would backup OK manually, but not in the context of a mtce plan? I’d greatly appreciate any help you can give.
Any recent changes on SQL server?
Any changes to SQL service accounts and ensure their passwords are not expired. Check DB maint.plan log for more information why those are failed.
Take help from thsi KBAhttp://support.microsoft.com/default.aspx?scid=/servicedesks/bin/kbsearch.asp?Article=288577 to troubleshoot the issue. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

quote:Originally posted by satya Any recent changes on SQL server?
Any changes to SQL service accounts and ensure their passwords are not expired. Check DB maint.plan log for more information why those are failed.
Take help from thsi KBAhttp://support.microsoft.com/default.aspx?scid=/servicedesks/bin/kbsearch.asp?Article=288577 to troubleshoot the issue. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi, thanks for your post. No changes to any SQL Users. No changes to any NT users, for that matter. SQLServerAgent runs as LocalSystem. No failures are reported in the maintenance plan log because the jobs never fail: they simply don’t finish. One new development: I removed the two offending databases from the maintenance plan, and now a new one has developed the problem. Could I be out of disk space somewhere specific (I have approx 15GB free on the drive), or reached a maximum limit of a system database? I disabled logging to msdb.sysdbmaintplan_history, but it didn’t help. Still stumped… Bob Shepherd
Have you looked under maint.plan log file for detailed explanation about failure, and also above KBA. If the job involves copying backups to network share and ensure to start SQL services with domain account, just in case. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hey satya, it is now 22:35 (I don’t know if you’re an hour still ahead or behind).<br /><br />It is friday evening. Are you still at work? [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
Thanks Frank, actually I was off last 2 days… had enough rest.. at home, so at my convenience.
GMT is behind its 21.45 here. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Seems to be hard to synchronize the UK with the rest of Europe…
Forgive me, …too much wine this evening!
Enjoy your weekend! Frank
http://www.insidesql.de
http://www.familienzirkus.de
quote:Originally posted by satya Have you looked under maint.plan log file for detailed explanation about failure, and also above KBA. If the job involves copying backups to network share and ensure to start SQL services with domain account, just in case. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, both. There are no errors logged. The routine simply never completes.
How about features of those 2 database giving trouble in Maint.plan. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

quote:Originally posted by satya How about features of those 2 database giving trouble in Maint.plan. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi, Thanks again for all your help. The problem went away after a reboot this weekend. Hardly as satisfying outcome…another unsolved mystery. As for database features, neither one has changed structurally, or had any large amount of data inserted recently. DBCC-CHECKDB reported no problems. I may never know what went wrong. Bob S.
As a lesson, ensure to keep an eye on SQL error log, event viewer logs and alerts are in place to know the issue if it strikes again. Ocassionally run SQLDIAG on the SQL server for information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

quote:Originally posted by satya As a lesson, ensure to keep an eye on SQL error log, event viewer logs and alerts are in place to know the issue if it strikes again. Ocassionally run SQLDIAG on the SQL server for information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Will do. Thanks again for your help.
]]>