SQL Process hung with PREEMPTIVE_OS_PIPEOPS as lastwaittype

  • :w00t: This is my Prod Server , I know this is P1,

    I was checking the disk space by using xp_cmdshell which inturn using WMI service, this was where my day went wrong. ON that I cancelled the query and killed te process but no luck. :w00t:

    KILL SPID with statusonly gives me

    SPID XXX: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

    It did not find any cmd process to kill. I have checked all possible sites in MS which says to restart the server or kill background process in task manager . I am not sure which will be the process in task manager.

    VB Script:

    ==============

    Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

    Set colDisks = objWMIService.ExecQuery _

    ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")

    If colDisks.count > 0 Then

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    Set colDisks = objWMIService.ExecQuery _

    ("Select * from Win32_LogicalDisk Where DriveType = 3")

    For Each objDisk in colDisks

    Wscript.Echo mid((objDisk.size)/1048576,1,8) & " " & mid((objDisk.Freespace)/1048576,1,8) & " " & "Logical Disk" & " " & objDisk.DeviceID

    Next

    Set objWMIService1 = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    Set colDisks1 = objWMIService1.ExecQuery _

    ("SELECT * FROM Win32_Volume WHERE DriveLetter IS NULL")

    For Each objDisk1 in colDisks1

    Wscript.Echo mid((objDisk1.Capacity)/1048576,1,8) & " " & mid((objDisk1.Freespace)/1048576,1,8) & " " & "MountedDrive" & " " & mid(objDisk1.Name,1,100)

    Next

    Else

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" _

    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    Set colDisks = objWMIService.ExecQuery _

    ("Select * from Win32_LogicalDisk Where DriveType = 3")

    For Each objDisk in colDisks

    Wscript.Echo mid((objDisk.size)/1048576,1,8) & " " & mid((objDisk.Freespace)/1048576,1,8) & " " & "Logical_Disk" & " " & objDisk.DeviceID

    Next

    End if

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • Killing threads through TM is a RISKY one.

    http://sqlblog.com/blogs/linchi_shea/archive/2010/02/04/killing-a-sql-server-thread-don-t.aspx

    Better Leave the rollback process continue, if you do not have any performance problem bec of that spid.

  • Thank you.

    This is my output when I ran sysprocess

    waittypewaittimelastwaittypewaitresourcedbiduidcpuphysical_iomemusage

    0x01E4179712580PREEMPTIVE_OS_PIPEOPS 1180112

    DO you think if this impact, also this the 3rd day and still it is in running state :w00t:

    Cheers,

    Got an idea..share it !!

    DBA_Vishal

  • Another reason not to use xp_cmdshell...

    Hung connection wait type PREEMPTIVE_OS_PIPEOPS

    Consider changing the process to a SQL Agent job that calls your script.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply