HEELPBOOK - Extreme wait-time when taking a SQL Server database offline ###################


##### SCENARIO

I'm trying to perform some offline maintenance (development database restore from live backup) on my dev database, but the 'Take Offline' command via SQL Server Management Studio is performing extremely slowly - on the order of 30 minutes plus now.


##### SOLUTION

There is most likely a connection to the DB from somewhere (a rare example: asynchronous statistic update)

To find connections, use sys.sysprocesses:


USE master SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MyDB')


To force disconnections, use ROLLBACK IMMEDIATE:


USE master ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE


############ ARTICLE INFO #############

Article Month: December
Article Date: 10/12/2012
Permalink: http://heelpbook.altervista.org/2012/extreme-wait-time-when-taking-a-sql-server-database-offline/comment-page-1/
Source: http://stackoverflow.com/questions/808232/extreme-wait-time-when-taking-a-sql-server-database-offline
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20
Google+ : https://plus.google.com/116990277568167008289/posts