Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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
share|improve this answer
    
Thanks @Matt. Will try the above solution and see if the problem disapears. –  viky Jun 1 '09 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 '09 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 '09 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 '09 at 16:41
add comment

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

share|improve this answer
    
@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 '09 at 10:34
    
@paul. Just to add its not system table. –  viky Jun 1 '09 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 '09 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 '09 at 14:23
    
+1 for trying to find the root of the problem. –  Matt Jun 1 '09 at 17:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.