3

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.

Improve this question

2 Answers 2

Reset to default
7

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
Improve this answer
4
  • Thanks @Matt. Will try the above solution and see if the problem disapears.
    – Viky
    Jun 1, 2009 at 9:21
  • @Matt. Tried to take the database offline and online. Dont know the cause but the problem is observed no more. Will try to dig a bit further.
    – Viky
    Jun 2, 2009 at 6:42
  • @Matt. Dont know the root cause,but I'll accept this answer as the problem got disappeared after trying above solution.
    – Viky
    Jun 4, 2009 at 8:07
  • This is exactly what I needed to clear the connections from a temp db I kept creating so that I could detach it.
    – Jack B Nimble
    Sep 11, 2009 at 16:41
4

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

Improve this answer
5
  • @paul. The issue is not database corruption. Actually am not sure of the issue. The db has one table which takes a lot of time when queried. either from program or from query analyser. Dev team says its a lock table. if any entity on a page is being used the edit control wont work. so to check the control in use they have this lock table. Hope it helps
    – Viky
    Jun 1, 2009 at 10:34
  • @paul. Just to add its not system table.
    – Viky
    Jun 1, 2009 at 10:38
  • Does restartin the database fix it? My guess is that it's a table design or fragmentation problem. Is there a clustered index on the table? Are all the queries against it turning into table scans?
    – Paul Randal
    Jun 1, 2009 at 13:41
  • Havent tried restarting database as there are several databases on the instance. Want to try to restart database and hence posted the question. confirmed with dev team that there are no clustered index on the table. not sure on the queries turning into table scans.
    – Viky
    Jun 1, 2009 at 14:23
  • +1 for trying to find the root of the problem.
    – Matt
    Jun 1, 2009 at 17:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .