Home / server / Questions / 16569
Accepted
Viky
Asked: 2009-06-01 21:33:40 +0800 CST

Restarting a database in a database instance without restarting entire database instance

  • 772

I have a database server running several instances of database. I am having issues while accessing a particular database and want to restart the same but without restarting the entire db server as it will effect other databases in the instance or other instances on the same server.

Is there a to restart a single database using commandline or any other means?

Is detaching the database from server and attaching it back or taking it offline and bringing back online same as restarting that database?

Note: Just to add its a MSSQL 2005 server hosted on win2k3.

sql-server database database-administration

2 Answers

  1. Best Answer

    I don't know if taking an individual DB offline and then putting it back online is equivalent to restarting the SQL service (from the DB's standpoint, at least - it's definitely not for the service), but it will "reset" the DB to the extent that it will close all existing connections and rollback any open transactions. If that's the effect you're after, then it might be sufficient, and it won't affect any other databases running on that SQL instance.

    From SSMS, you can use this SQL:

    -- 'rollback immediate' will disconnect existing users w/out
-- waiting for transactions to finish.
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE MyDatabase SET ONLINE
go
    • 7

  2. What are the issues that you're seeing when accessing that particular database? If it's database corruption, restarting the database won't have any effect, but is often tried.

    Just in case it is corruption, you need to figure out the extent of the problem: DBCC CHECKDB (yourdb) WITH NO_INFOMSGS, ALL_ERRORMSGS

    And then either restore from your backups, extract data into a new database, or as a last resort run a database repair. Lots of pros/cons to each of these, depending on what you have wrong - but I won't flood this topic with details and links unless you actually do have corruption issues. And if it is corruption, you can always call Product Support to help you recover in real-time rather than relying on the forum.

    Thanks

    • 4