SQL Process hung with PREEMPTIVE_OS_PIPEOPS as lastwaittype

Question

Post reply

SQL Process hung with PREEMPTIVE_OS_PIPEOPS as lastwaittype

vkundar

Hall of Fame

Points: 3431
More actions
October 5, 2012 at 12:35 am

#262627

: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

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

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

SQL Show SSCertifiable Points: 6143 More actions · Answer 1

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.

vkundar Hall of Fame Points: 3431 More actions · Answer 2

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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