February 26, 2010 at 7:43 am
Hi all,
I have problem that one database cannot startup because that background process is blocked from another process.
The strange part is that process which is blocking database startup is SPID "-3". I know that negative spid's are related with MSDTC but I am not sure in that in this situation. WHY? Because before I also had problem with MSDTC and I resolved problem with killing that process KILL (UOW), but this time UOW is 00000.... and I can't kill it.
Because all of this problems my SQL Agent is not working properly.
Does anyone have any ideas about this?
February 26, 2010 at 8:30 am
Is it possible the process you killed is still rolling back? That's what it sounds like to me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2010 at 9:41 am
No it is not possible.
SPID 23 (background process) is blocked by -3 process. I don't know what is -3 but I know that 23 is trying to recover one database.
The strange things in this situation are:
1. Why I can't kill SPID -3 (please don't tell me that I need to use UOW from syslockinfo 🙂 )
What option I have?
1. Reinstall SQL Server
2. Find how to kill -3 spid and after that kill 23 spid
First thing I know how to do, but I still believe that there must be another way to resolve this issue.
February 26, 2010 at 1:41 pm
Try restarting the application(s) concerned(involved in tht hung transaction) and hope that it has a recovery mechanism to deal with any MSDTC transactions that were left unresolved.
February 26, 2010 at 2:14 pm
negative spids can be related to MSTDC having problems to handle a distributed transation.
have a look at http://robkraft.spaces.live.com/Blog/cns!E6687F3AB6372637!161.entry?wa=wsignin1.0&sa=14956856
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 4, 2010 at 5:09 am
I know that (please see my first post again)
March 5, 2010 at 12:58 am
What other info can you provide ??
use master
select distinct req_transactionUOW from syslockinfo
Is SQLServer errorlog showing any related info ?
Is SQLAgent SQLAGENT.OUT showing any related info ?
Did your sqlinstance get out of this situation by itself or did your stop / start it ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 25, 2010 at 3:25 pm
Hi,
This query was return only zeroes.
No I didn't saw anything in the log files which I could correlate with this strange behavior.
I tried to restart server but after start everything was same.
I decide to reinstall SQL Server because lack of time 🙁
March 26, 2010 at 2:21 am
SPID -2 is related to DTC. Spid -3 is not. (and neither is -1)
Spid -3 is a deferred rollback. In 2005/2008 Enterprise edition, SQL will bring a database online at the end of the roll-forward stage of restart-recovery and will continue the rollback while the database is online and in use.
In order to protect data that still needs to be rolled back, SQL locks it, and the owning SPID for that will be -3.
If you look in Books Online under the entry for sys.dm_tran_locks (the DMV that replaces the deprecated syslockinfo), you'll see this:
request_session_id
Session ID that currently owns this request. The owning session ID can change for distributed and bound transactions. A value of -2 indicates that the request belongs to an orphaned distributed transaction. A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.
I assume that SQL had restarted shortly before them.
Has the issue been resolved yet? (I would hope, it's almost a month later). If not, I strongly suggest that you contact Microsoft's customer support and get assistance with this problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2010 at 6:52 am
Gila sorry for late response.
Problem was resolved. Only thing that we did was that we wait rollback/rollfoward process to finish 🙂
p.s
Excellent post!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply