Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Lock Issue Advice Expand / Collapse
Author
Message
Posted Friday, October 26, 2012 8:35 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:01 AM
Points: 782, Visits: 729
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.

Post #1377632
Posted Friday, October 26, 2012 8:42 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:01 AM
Points: 782, Visits: 729
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
Post #1377638
Posted Friday, October 26, 2012 4:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:34 PM
Points: 565, Visits: 458
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.
Post #1377890
Posted Monday, October 29, 2012 6:38 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 9:01 AM
Points: 782, Visits: 729
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.
Post #1378221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse