Startup and Shutdown + Tsql sqlserver 2005

Question

Startup and Shutdown + Tsql sqlserver 2005

gaganks

Mr or Mrs. 500

Points: 585
More actions
October 28, 2009 at 4:54 am

#212627

Hi ,
Is there a Way to shutdown and startup a single Database (like Oracle) in sqlserver.
I know that there is a T-sql command which can be executed on SSMS.
i.e SHUTDOWN
and SHUTDOWN NOWAIT
But does a bounce of the Whole instance and not indiviudually.
Any help appericiated..
Thanks,
Gagan

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

you don't really shut down a database, but you can kick everyone off so you have exclusive access; you might do that so you can restore the database, or detach it for example:

USE [master]

GO

ALTER DATABASE [YOURDATABASENAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I've seen examples that immediately rename the database after the above command so if anyone tries to reconnect right after that command, it's not there.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

You can take the database offline as follows:-

ALTER DATABASE [dbname] SET OFFLINE

Lowell SSC Guru Points: 323507 More actions · Answer 3

ahh good point Ian; forgot about that one;

i just tested, and if anyone is connected, you need to kick them off with Ians command as well:

ALTER DATABASE [SandBox2000] SET OFFLINE WITH ROLLBACK IMMEDIATE

knocks em out.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 734826 More actions · Answer 4

Offline works great if you don't need to access the instance. I like that.

But if you want to kick everyone out, Lowell's first command is what I thought of.

why do you need to do this? Is there something that you want to do with the database?

gaganks Mr or Mrs. 500 Points: 585 More actions · Answer 5

Thanks Lowell,Ian for the quick response..

Steve,

I sometimes have questions asked to bounce a single db by Application team(rather the whole instance) as that is possible in Oracle. and also sounds correct as other db might have some transactions going around and might disturb if i have to bounce a Instance.

And BTW do we have a T SQL command to startup a single user Database.

Thanks in Advance,

Gagan

Steve Jones - SSC Editor SSC Guru Points: 734826 More actions · Answer 6

Why bounce the instance? What do they think that does? If you bounce a db, you aren't necessarily doing anything to the server. I'm not even sure if the cache clears. That would be interesting to find out, but that doesn't help performance or necessarily clear the server.

Instead you might want to kill offending SPIDs

gaganks Mr or Mrs. 500 Points: 585 More actions · Answer 7

Sorry Guys ..I am trying to compare SQL Server to Oracle..

But Would like to understand if i can have a bounce of a particular User DB.

Thanks,

Gagan

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

gaganks (10/28/2009)
I am trying to compare SQL Server to Oracle..

Happens that at this level SQL Server and Oracle are really different.

On the Oracle side an "instance" is actually a database so saying that you shutdown/startup an "instance" is the same as saying you shutdown/startup a "database".

On the SQL Server side -by design an "instance" runs several "databases" therefore terms are not interchangeable.

Whish this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Steve Jones - SSC Editor SSC Guru Points: 734826 More actions · Answer 9

I don't think you accomplish anything with the bounce. The "rollback immediate" will kill sessions.

If you bounce the instance, then memory, cache, etc. reset.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 10

Steve Jones - Editor (10/28/2009)
If you bounce the instance, then memory, cache, etc. reset.

Exactly!

That's the difference in non-RAC Oracle you get a one-to-one relationship in between instance and database while in SQL Server you get a one-to-many relationship.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

mithalsnothon Newbie Points: 1 More actions · Answer 11

This site Really nice for me....There are many information available here.........

AcneZine Review