MSQL_XP Wait type


MSQL_XP
This kind of wait type occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends. In simple word SQL Server lost the control of extended stored procedure.

Few days back LiteSpeed backup job are running longer than normal, when I checked the process status using Activity Monitor, it shows processes are running but in MSQL_XIP wait state.

Activity Monitor

I tried to KILL the backup sessions and it gone into KILLED\RollBack state forever.

You can either reboot the server to free/terminate all the session or there is another way that you can find the SQL Server Backup Process associated Operating System Id.

You can follow the below steps to find out SPID related KPID

Step 1: Execute SP_WHO2 active command, you will SPID related KPID which is OS Process ID.
OR
Step 1: Check for DMVs to get the OS Process ID

Step 2: To get the more information regarding the OS Process download the Process Explorer from the below link.

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Step 3: Once confirmed you can kill OS process using Task Manager.

Advertisements

3 thoughts on “MSQL_XP Wait type

  1. chetan

    Hi Jugal,

    If we have a restart then won’t the rollback process will start all over again. If not then why ? does this gurantee consistancy of the database ?

    Thanks,
    Chetan

    Reply
  2. Hany

    Thanx 4 the post, but as you said: “Execute SP_WHO2 active command, you will SPID related KPID which is OS Process ID.”, I just didn`t get it how to map the OSPID from the SPID? can u plz elaborate more.

    Thanx in advance.

    Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s