Wait Type MSQL_XP on a log backup job

ChrisAVWood

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

ChrisAVWood

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

shawnnwf

I am having the same issue. Any update on the issue or a resolution?

ChrisAVWood

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

joetig

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?

ChrisAVWood

Joetig,

You don't mention what version you are running?

Chris

joetig

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].

ChrisAVWood

Joetig,

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

Chris

joetig

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.

ChrisAVWood

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

joetig

Yes, that is what we thought. I guess we will have to wait for the next maintenance window.

joetig

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?

:?:

joetig

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