Restarting a database in a database instance without restarting entire database instance
Solution 1
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
Solution 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
Related videos on Youtube
Yuri
Updated on September 17, 2022Comments
-
Yuri over 1 year
Is there a way to dismiss the keyboard from MFMailComposeViewController ? If the user rotates the device, I am loading a separate controller without "send" or "cancel" being pressed, and the keyboard is remaining on screen. Is there an way to dismiss the keyboard without "done" or "send" being pressed?
-
Viky almost 15 yearsThanks @Matt. Will try the above solution and see if the problem disapears.
-
Viky almost 15 years@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 almost 15 years@paul. Just to add its not system table.
-
Paul Randal almost 15 yearsDoes 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?
-
Viky almost 15 yearsHavent 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.
-
Matt almost 15 years+1 for trying to find the root of the problem.
-
Viky almost 15 years@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 almost 15 years@Matt. Dont know the root cause,but I'll accept this answer as the problem got disappeared after trying above solution.
-
Jack B Nimble over 14 yearsThis is exactly what I needed to clear the connections from a temp db I kept creating so that I could detach it.