What is the Use of On-Line Off-Line in ms sql.

Question

What is the Use of On-Line Off-Line in ms sql.

parthi-1705

SSCrazy Eights

Points: 9277
More actions
July 20, 2009 at 9:20 am

#133101

HI
What is the Use of On-Line,Off-Line in ms sql.
Whether it can be used to reduce the TempDb Size by bringing TempDb to Offline and then Online(Is it is Possable to move TempDb OffLine)
Thanks
Parthi

Viewing 4 posts - 1 through 3 (of 3 total)

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

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 1

parthi (7/20/2009)
HI
What is the Use of On-Line,Off-Line in ms sql.
Whether it can be used to reduce the TempDb Size by bringing TempDb to Offline and then Online(Is it is Possable to move TempDb OffLine)

I take database offline when i don't want people to connect to it...But i may want to bring it backonline at some point...for example if i migrate a database between servers or take a database to a new version. I don;t want anyone connecting to the old database but i might want to revert back to it if something goes awry and i want to revert back to old database

I don't think you can take system databases offline, including tempdb

Gethyn Elliswww.gethynellis.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If the question was urgent (as you stated), you should have checked Books Online. It's faster than waiting for someone to reply.

Books Online:

OFFLINE
The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline.
ONLINE
The database is open and available for use.

You can't set that option on TempDB. The TempDB database must always be available while the service is running.

If you try, you'll get an error:

Msg 5058, Level 16, State 4, Line 1

Option 'OFFLINE' cannot be set in database 'tempdb'.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 734587 More actions · Answer 3

I use it for the same reasons, but I don't believe you can do this with tempdb.

tempdb is used for all sorts of operations in databases. If you take tempdb offline, likely it's the same as taking the application databases offline. Things will stop working.