5

I am an application programmer. We have a SQL Server 2012 instance that hosts many related databases (around 12 databases). The vendors did some changes to one of the database and the application and they want us to reboot the database server. I did some research and found out that rebooting a db server is always not a great option unless changes have been made in the server configuration. Is there a way to restart just that database? Would that help?

11

You can cycle the database by doing:

alter database YourDatabase
set offline
with rollback immediate; -- this line will rollback ongoing transactions
go

And then to bring the database back online you can do:

alter database YourDatabase
set online;
go

This is a way to "restart" the database itself directly within SQL Server, without bouncing either the SQL Server service(s) or the server operating system itself.

As for the question of "would that help?", there is no way for us to answer that. You should be asking the vendor why do I need to restart the database server? Oftentimes people that don't know what they are particularly doing will cycle an entire environment after a migration/upgrade/change. Why? "Just in case".

If the vendor made a change directly to the database (DDL/DML, whatever) then that change takes effect immediately. So you really need to follow up with the vendor to find out why they want the database server restarted.

| improve this answer | |
  • 1
    +1. I'd be concerned about this vendor's work if this is their level of understanding DDL / DML changes. – Eric Higgins Dec 3 '14 at 18:26
  • The vendor said that she is unable to perform an action in the front end and when she consulted her developers , they said that it was a database issue. I asked her to send me the error file and the error file is throwing some 308 error stating that an index specified in the from clause does not exist. But when I queried the dmv i am able to see the index. so, does cycling the database resolve this issue? how can we fix this? – Info23 Dec 3 '14 at 18:33
  • 3
    @Info23, a database error pertaining to a missing index will not be resolved by cycling the server or the database. – Daniel Hutmacher Dec 3 '14 at 20:02
  • 3
    @Daniel Agreed - sounds like if anything they need to cycle the app, web, etc. servers. I'd be cautious going forwards of this vendor's database recommendations/instructions. – LowlyDBA Dec 3 '14 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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