June 25, 2008 at 4:04 am
select * from master.dbo.sysprocesses
where blocked > 1
is showing a spid blocked by same spid.
sp_who is showing 3 rows for this spid with different ECID.
The command running was alter index rebuild with (online = on)
tempdb has 2 data files.
SS2K5 x64 Enterprise edition.
Server has 2 processors.
Can a spid block itself? Is it a bug?
kill is not ending it. How to fix this?
June 25, 2008 at 5:05 am
I believe this is when parallel processing is used and sometimes the SPID does block itself, although usually only for a very small amount of time before it is released. I have never had an occasion where it has deadlocked itself.
June 25, 2008 at 6:35 am
I believe this is when parallel processing is used and sometimes the SPID does block itself, although usually only for a very small amount of time before it is released. I have never had an occasion where it has deadlocked itself.
I have encountered this on paraell processing servers and SPIDS can block themsleves. I don't think its a bug its just waiting for for one spid to finish so it can access the ame resource.
Gethyn Elliswww.gethynellis.com
June 25, 2008 at 6:54 am
Here is a catch, you can look in the and make sure the waittime column is low OR the waittype of the SPID is a latch waittype, so there should be nothing to worry about, this behaviour was available as a detailed output after SQL 2000 SP4 and even 2005.
Nothing to worry about, check this link: http://support.microsoft.com/default.aspx/kb/906344
Maninder
www.dbanation.com
June 25, 2008 at 7:34 am
These are page latches and are supposed to be there. SQL 2000 sp4 introduced these being included in the process reporting.
Ignore them.
June 25, 2008 at 10:25 pm
Thanks to all those who replied.
Thanks to Mani Singh for the link.
Suresh
June 29, 2008 at 5:01 pm
Hi there this is a bug with service pack 4 look on microsofts website it tells you there what to do.
Regards,
Terry
June 30, 2008 at 12:40 am
Thanks Terry,
Do you have the link?
Suresh
June 30, 2008 at 7:04 am
http://support.microsoft.com/kb/271509
This this is what you want, T
June 30, 2008 at 7:27 am
kill is not ending it. How to fix this?
-----------------------------------------
When you kill the SPID, DOes it go into a ROLLBACK Situation and stay there right?
It gets converted into a Phantom Process, which can only be freed once you recycle your SQL Server Service.
There are other Tricks to get RID of Such Process, but that require a lot of Patience and Work.
I Would Personally reccomend you to now TRY This on PRODUCTION Server..EVER... unless you know what you are up to and how to read the SYSTEM Processes.
you can catch hold of Microsoft sysinternals PROCESS MONITOR and Drill
down to the Processes under MSSSQLSErver.
compare them with sysprocesses SPID Row and you will see what i mean.. It is too complex to list down here...
But YES once you kill these processes, they go into a ROLLBACK Process which stays there unless the SQL Server is Recycled. They however do not Hurt the system.
Maninder
www.dbanation.com
July 1, 2008 at 4:47 am
Thanks Terry for the link.
Thanks Mani Singh.
I restarted the SQL Server service. But it didn't get restarted. Status was "Stopping".
Non-administrator users were not able to login.
So, I rebooted the server.
Suresh
August 11, 2008 at 12:14 pm
Is there any solution for this problem
That is a feature but Still....it hurts the server performance so bad.
In my case the Process never gets released unless I kill it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply