October 26, 2012 at 8:35 am
I am going to start this with an apology - I have a fairly good amount of understanding of SqlServer administration but am a little bit stretched beyond my limits on this problem.
The problem presents itself with being unable to run the queries I use to do index maintenance - waiting on LCK_M_IS,TAB: 2:1686021904:0 (temp db).
During my investigation into the issue I notice a session that has been open for 2 days (as of the writing of this it is 4 days). Since this was a connection from one of our web servers I killed the session. It has been in a kill/rollback since. It has a wait of PAGELATCH_EX,2:4:244606, with the owner of the lock on this resource is the session itself. There are no other locks on Tempdb.
Short of restarting the SqlServer services is there anyway to resolve this? It is currently having no production impact that I am able to determine, but that could change with time.
October 26, 2012 at 8:42 am
Forgot to include this: Messages in the Error Log
Time out occurred while waiting for buffer latch -- type 4, bp 000000009FFC4D00, page 4:244606, stat 0x4c00009, database id: 2, allocation unit id: 423767696343040/142292719632384, task 0x0000000006EB7948 : 0, waittime 318600, flags 0x100000003a, owning task 0x0000000006EB7948. Continuing to wait.
I take challenges like these as learning opportunities
October 26, 2012 at 4:02 pm
I'm confused - is the query that you killed the query that you were using to do index maintenance? And that was running for 4 days before you killed it? Or it was the query from your web server that was running for 4 days before you killed it?
I always hesitate in restarting SQL Server. Always a last resort. If it was the query from your web server that was running for 4 days before you killed it, do you have any idea what it was doing? Query text? If it was an update, insert, or delete, you may need to wait as long as 4 days (sometimes more) for the query to completely rollback.
October 29, 2012 at 6:38 am
TheGreenShepherd (10/26/2012)
I'm confused - is the query that you killed the query that you were using to do index maintenance? And that was running for 4 days before you killed it? Or it was the query from your web server that was running for 4 days before you killed it?I always hesitate in restarting SQL Server. Always a last resort. If it was the query from your web server that was running for 4 days before you killed it, do you have any idea what it was doing? Query text? If it was an update, insert, or delete, you may need to wait as long as 4 days (sometimes more) for the query to completely rollback.
The query killed was a session from the webserver. This session was killed after 2 days of running, and has been in a rolling back state since. I think the issue is a page lock on tempdb, and the rollback process is waiting for its own session to clear for it to rollback.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply