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.
[caption id="attachment_713" align="aligncenter" width="600" caption="Activity Monitor"]
[/caption]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.
No comments:
Post a Comment