Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lock Issue Advice


Lock Issue Advice

Author
Message
crashdan
crashdan
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 757
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.
crashdan
crashdan
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 757
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
TheGreenShepherd
TheGreenShepherd
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 591
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.
crashdan
crashdan
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 757
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search