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

Session hangs Expand / Collapse
Author
Message
Posted Wednesday, April 23, 2014 10:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
Hi friends

Executing procedure on management studio usually takes 10 minutes, it took over an hour today. I had no luck to see if there were any locks that is causing the slowness because the sql queries to check locks did not work on our client production db.. May be it's some permissions issue that I'm not able to run sql queries on the master database to check the locks.. So I had no go but to kill the session on my management studio. Rollback has been running for about 2.5hrs now.. I checked the rollback status I get 'SPID 121: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. So it's clearly not doing any roll back here. Is there a way to kill the process at the OS level? Since it's production we do not want to bounce the service...

Please help.. thanks much
Post #1564384
Posted Wednesday, April 23, 2014 11:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
Restart SQL.

There isn't a process at the OS level to kill (and if you have the idea of killing threads within SQL from the OS, that'll likely crash the SQL service at best)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1564395
Posted Wednesday, April 23, 2014 11:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:06 PM
Points: 319, Visits: 757
I see several temp db locks request-mode SCH-M.. Can the locks in temp db affect other databases?

Thanks
Post #1564399
Posted Wednesday, April 23, 2014 1:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
No.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1564448
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse