May 17, 2011 at 1:29 pm
Hi, currently we have deployed a new application. (our environment is SQL Server 2008 EE x64 SP1 failover cluster). Today I have run sp_estimate_compression_savings on the biggest table of application which is approx 10 GB. The process did not finish for 1 hour so I clicked cancel on SSMS, it took a long time too, when I checked dm_exec_requests I saw that it is blocked by its own spid. I tried again a second sp_estimate_compression_savings but it stuck up the same way. I issued kill to both, they become KILLED/ROLLBACK but they are still there and now I have two processes stuck up as KILLED/ROLLBACK, status: suspended, blocking_session_id = its own id. Also wait types:
LCK_M_SCH_M16513138LCK_M_SCH_MOBJECT: 2:1370669618:0 11
In sys.dm_tran_locks, the session has 17 Sch_M lock on the same object in tempDB, 15 has different lock_partition_id and GRANT, 2 has lock_partition_id 0 one GRANT one WAIT. It seems the process is waiting for its own lock??
Anyone has encountered this situation? The only different thing I can say for this db is it has full-text indexes. Is there a way to resolve the locks, rollback the process?
This is a consolidated server and other apps are 7/24, we are not willing to reset it. Also I cannot detach the db because it doesn't go to SINGLE_USER state because of open sessions.
Best Regards,
Serter
May 17, 2011 at 2:26 pm
What you are describing sounds a lot like "intra-query parallel thread deadlocks". I am working on a similar problem..
CEWII
May 18, 2011 at 12:37 am
I guess so too, any thoughts how to resolve it? Changed maxdop to 1, but no change..
May 18, 2011 at 1:54 am
Are you encountering parallelism even you change it to MAXDOP1?
May 18, 2011 at 2:05 am
No, I set it to 1, to see if the self blocking sessions will resolve, but no change..
May 18, 2011 at 2:43 am
I have encountered this problem in SQL Server 2000 itself, but we could recycle the instace as there was a acceptable downtime for that application.
Is it possible to put the db in offline mode, I am not sure if there is blocking?
M&M
May 18, 2011 at 3:20 am
looping Myself
Thanks
May 23, 2011 at 3:14 am
Hello, any luck regarding this issue?
May 23, 2011 at 3:16 am
Nope, sessions are still there in KILLED/ROLLBACK status, I think they will be until service restart.
May 23, 2011 at 4:29 am
I did restart SQL Server when it was possible and issue seems to be resolved.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply