[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Possible to restart single database in SQL Server 2005?

Hello Experts,

     I have a SQL Server running about 3 databases.  1 is hit pretty hard from time to time by our data analysts.  Every now and then that single database begins to respond very slowly to queries that make it hard to work with.  When this happens a reboot of the server after hours will resolve the issue for them.  Is there a better way to handle this without rebooting the server or that won't affect the other databases?  
0
bobohost
Asked:
bobohost
3 Solutions
 
strickddCommented:
It sounds like there are long running queries, abandoned queries, or open connections. I would check for those and fix the root of the problem first. The next thing you can do is to take the database offline and the bring it back online (via right-click menu). That will probably do what you need it to.
0
 
Simone BCommented:
I agree with strickdd, it would be best to find the cause of the problems. Meantime, this is from serverfault.com, and may be helpful in the short term:

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

Here's the link to the forum:

http://serverfault.com/questions/16569/restarting-a-database-in-a-database-instance-without-restarting-entire-database-i
0
 
bobohostAuthor Commented:
Where can I go to see if there are abandoned or running queries?  I'm not a SQL admin just trying to help out these guys until they find somebody.
0
 
thameboCommented:
SQL Managment Studio 2008R2 Express edition. After you have installed it look at the last icon on the toolbar menu called "Activity Monitor". That will show you all the information you want.

http://www.microsoft.com/download/en/details.aspx?id=22985
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now