May 2, 2014

We may need to take a database offline various reasons, for example, it was a test database and we don’t need it anymore or the application has been decommissioned, etc. Sometimes, I have noticed that Sql Server Management Studio becomes unresponsive and hangs for a long time while taking the database offline. In this post I will talk about how to fix this issue.

Usually, the database can be taken offline by right-clicking on the name of the database and clicking Tasks > Take Offline as shown below.

image

Once I click on “Take Offline”, I expect the database to be taken offline but instead it hangs. To avoid the issue, I can write sql script to take the database offline. Following is the script that will take the database offline and then bring it online.

Take Database Offline / Online
  1. --take offline
  2. USE master
  3. GO
  4. ALTER DATABASE [database_name]
  5. SET OFFLINE WITH ROLLBACK IMMEDIATE
  6. GO
  7.  
  8. --take online
  9. USE master
  10. GO
  11. ALTER DATABASE [database_name]
  12. SET ONLINE
  13. GO

0 comments:

Reference: Shahed Kazi at AspNetify.com