What are the challenges you face when working across database platforms? Take the survey
Options

Wait Type MSQL_XP on a log backup job

ChrisAVWoodChrisAVWood Posts: 361
edited February 2, 2010 8:18AM in SQL Backup Previous Versions
Hi,

Running 5.3.0.178 on SQL2005 build 3239 I have a job that is stuck with a waittype of MSQL_XP. I understand this means waiting on an extended stored procedure, in this case sqlbackup. All the other backup jobs have run successfully while this one just waits.

Any ideas?

Thanks

Chris
English DBA living in CANADA

Comments

  • Options
    I had cancelled the original SQL Agent job and this worked after a while. The spid is still there and a KILL spid id WITH STATUSONLY shows an estimated rollback completion of 0% but the backup job has now run successfully the next time is was scheduled in SQL Agent. Red Gate GUI shows it still running. Strange.

    Chris
    English DBA living in CANADA
  • Options
    I am having the same issue. Any update on the issue or a resolution?
  • Options
    Red Gate have sent me a new version of SQLBackup. This is supposed to fix this problem yet I had only seen it once so proving it fixes the problem will not be easy.

    Chris
    English DBA living in CANADA
  • Options
    We have also encountered the same issue. I recently created a job to email us when it has detected that a job has been running longer than the max time + 20% in the past week.
    Upon implementation, I discovered two processes that are runnable. When I perform an dbcc inputbuffer on these tasks, they show up as the execute sqlbackup command. Both of these processes have been in the system for over 30 days.
    I attempted to kill the process, but it did nothing, now it appears as killed/rolledback. I restarted both the sql agent and the redgate and neither one helped.
    It looks like only the restarting of the server will be my last alternative which I will wait for some scheduled downtime.
    Does anybody have any ideas?
  • Options
    Joetig,

    You don't mention what version you are running?

    Chris
    English DBA living in CANADA
  • Options
    Version v5.3.0.178 same as previous post. I received version upgrade to 5.4.

    My issues is with the processes that appear when you run sp_who2. They are not running, they can't be killed [it appears as killed\rollback].
  • Options
    Joetig,

    Version 5.4 I believe should fix the problem. I certainly haven't had it since upgrading to 5.4

    Chris
    English DBA living in CANADA
  • Options
    Yes, that is good to hear. But, how do you get rid of the processes that still show in sp_who2. They cannot be killed and I am trying to avoid restrting the sql server.
  • Options
    Joetig,

    We reboot our servers at least once a month for Microsoft Patch Maintenance so that probably would have killed the orphaned process. It happened a long time ago and I do not have exact records.

    Sorry

    Chris
    English DBA living in CANADA
  • Options
    Yes, that is what we thought. I guess we will have to wait for the next maintenance window.
  • Options
    Upgraded from Version v5.3.0.178 to 5.4.0.55
    The issue went away for 9 months, but has come back in the last week or so.

    The only difference is we applied SP2 CU12 service pack to SQL 2005 this past december

    Do we have any solutions for this problem?

    :?:
  • Options
    We found the issue. It appears that after applying the cu12 service pack, the machine was failed over. The other server was not running the 5.4.0.55 version. We suspect that the original redgate cluster upgrade did not work properly.
    We are now running on version 5.4.0.55
Sign In or Register to comment.