SQL Process hung with PREEMPTIVE_OS_PIPEOPS as lastwaittype

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Process hung with PREEMPTIVE_OS_PIPEOPS...

SQL Process hung with PREEMPTIVE_OS_PIPEOPS as lastwaittype

Rate Topic

Display Mode

Topic Options

Author

Message

vkundar

Posted Friday, October 05, 2012 12:35 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 83, Visits: 414

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

Post #1368844

Posted Friday, October 05, 2012 7:31 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 303, Visits: 490

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.

Post #1369030

vkundar

Posted Saturday, October 06, 2012 4:30 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 83, Visits: 414

Thank you.

This is my output when I ran sysprocess

waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage
0x01E4 179712580 PREEMPTIVE_OS_PIPEOPS 1 1 80 11 2

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

Post #1369409

opc.three

Posted Monday, October 08, 2012 9:27 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 6,724, Visits: 11,771

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1370144

« Prev Topic | Next Topic »

Permissions