January 20, 2012 at 12:12 pm
I have a open transaction when I was doing an update, I forgot to commit it.
now its giving me trouble,
I can see it as a SPID 30 in sp_who2 results
since I didnt give the transaction any name, how can I commit it now ?
January 20, 2012 at 12:17 pm
SPID 30? Sure about that, because that's a system SPID, not a user one.
To commit a transaction you just run COMMIT TRANSACTION from the same connection that you started it. If you close the connection without committing, the transaction is automatically rolled back.
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
January 20, 2012 at 12:21 pm
well its still there and yes the connection is closed.
it says
Status: background
Command : Update
CPU Time :91156
DISK IO 6371
SPID : changed to 16 now.
its locking database and service broker has stopped working because of this open tran
January 20, 2012 at 12:32 pm
As Gail says that will be a system SPID.
Run DBCC INPUTBUFFER (16) and see what the statement is
January 20, 2012 at 12:56 pm
Session ids that low are system connections, not user connections. You couldn't have opened a transaction on one of those connections. Also, an open connection can't change spids, if previously spid 30 had an open transaction and now spid 16 does, those are two different sessions doing different things.
What are they doing (command in sys.dm_exec_requests)
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
January 20, 2012 at 1:24 pm
that doesnt tell me what command is running.
There is definately a blocking issue as even drop table and delete from is taking forever to delete one row from a table ??
when I check sp_who2 there is no blocking, I even restarted the server just now.
still drop table command is stuck
January 20, 2012 at 1:26 pm
WangcChiKaBastar (1/20/2012)
that doesnt tell me what command is running.
It tells you what the process is. So please, what is the command as shown in sys.dm_exec_requests?
Also, what is the wait type (and if set wait resource) for the 'hung' drop table and delete statements?
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
January 20, 2012 at 1:41 pm
wait type is NULL
that 'Open Tran' I am talking about is blocking the drop as is evident from SP_Who2 'Blkby' column
January 20, 2012 at 1:45 pm
I think here is what you needed Gail
session_id status blocking_session_id wait_type wait_time wait_resource transaction_id
52 suspended 27 LCK_M_U 4563 RID: 8:1:4172:0 14706
January 20, 2012 at 1:45 pm
If something was blocking the drop, then the session running the drop would not have a wait type of null, blocked by definition means waiting.
One last time...
What are the values for the command column for the two system sessions that you've seen causing problems? Query sys.dm_exec_requests.
What are the wait types and wait resource for the sessions that are running the drop table and the delete? Query sys.dm_exec_requests.
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
January 20, 2012 at 1:53 pm
What are the values for the command column for the two system sessions that you've seen causing problems? Query sys.dm_exec_requests.
UPDATE
What are the wait types and wait resource for the sessions that are running the drop table and the delete? Query sys.dm_exec_requests.
WAIT Type : LCK_M_U
Wait Resource: RID: 8:1:4172:0
January 20, 2012 at 2:02 pm
Interesting...
What's the Last_wait_type for those two system sessions? (16 and 30)? Same DMV.
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
January 20, 2012 at 2:09 pm
after killing the SPIDS repeatedly it seems to have resolved now. wierd !!
thanks Gail
January 20, 2012 at 2:25 pm
Very weird... Are you using Service Broker at all?
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
January 20, 2012 at 3:49 pm
Yes I am using Service Broker.
Main problem is that service broker has stopped working and I think it may be because of this issue
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply