How to take a table offline?

Question

How to take a table offline?

ramadesai108

SSChampion

Points: 10609
More actions
December 15, 2010 at 1:12 pm

#146552

How to take a table offline?

Viewing 10 posts - 1 through 9 (of 9 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Not sure what you mean.

If you want to lock down the contents so nobody can access it, you can that through the security rules. You'll start getting error messages from every piece of code that tries to access it, so that may be a bad idea.

Can you clarify what exactly you're trying to do?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Agreed with Gus above, more information about what you're trying to do would help.

If you're just trying to see if anyone uses it (the old, shut it off and listen for the screams method of obsoletion removal), I usually use an sp_rename to something like zzzzz_tablename.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

Have databases where I don't know which tables are being used and what not. So what I want to do is which ever table I think is not being used, I want to disable it but not drop it at this point. Any ideas?

Thanks.

grahamc SSCertifiable Points: 6762 More actions · Answer 4

What I used to do, once I had worked out what I was pretty sure was unused, was renamed the object with an "_" in front of the name.

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

ramadesai108

SSChampion

Points: 10609

December 16, 2010 at 6:52 am

#1263420

Great idea. Thanks.

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 6

This post caught my interest because it is recommended that a compressed index be dealt with offline:

https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

So to address the issue as to why one may want to take the server offline, it is likely that this would happen in the process of compressing or rebuilding a heap. I wasn't completely sure of the why's and wherefore's involved here. Perhaps all that was addressed in the above article was that the initial scan can use all processors (as opposed to one) if the database is offline when the heap is compressed.

Jamie

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

You can't do anything to an index when the database is offline though. Offline database means inaccessible.

I suspect the article means offline as in, don't use the ONLINE = ON option on the index build/rebuilt. It's definitely not going to be the database or server that's offline.

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 8

Summer90

SSC-Dedicated

Points: 32891

June 4, 2015 at 5:47 am

#1802950

I would simply rename the table.

darius.england Mr or Mrs. 500 Points: 557 More actions · Answer 9

I know it's an old thread but... Let me tell you this: if a table is clustered, then it's enough to DISABLE the cluster. Nobody will be able to access the table, not even an admin. This is because a clustered index IS THE TABLE.