indexing online vs offline, and setting a database offline

Question

Post reply

indexing online vs offline, and setting a database offline

klineandking

SSCrazy

Points: 2570
More actions
January 19, 2010 at 9:28 am

#215652

Good morning people, my questions is about reorganizing or rebuilding indexes online vs offline, i do not know what this actually means or how its done. and my next question is what would be the reasons for taking a database offline

Viewing 15 posts - 1 through 15 (of 15 total)

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

klineandking (1/19/2010)
Good morning people

Very good morning 😉

my questions is about reorganizing or rebuilding indexes online vs offline, i do not know what this actually means or how its done

1.Index rebuild works by re-creating the index internally again and when that has been achieved, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.

2.During the index rebuild process, the statistics are also re-computed – same as when a new index gets created. Reorganize on the other hand does not update the statistics. Reorganize essentially just swaps one page with another and thus does not require free space for this operation like rebuild does. Infact, reorganize can free up some pages as it does the reorg in two phases – compaction and defrag.

3. Online Rebuilds of Indexes are supported in Enterprise Edition only..

and my next question is what would be the reasons for taking a database offline

Depends on what you are trying to do?

HTH...

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

klineandking SSCrazy Points: 2570 More actions · Answer 2

Thank you for the reply i appreciate it i would like to know what does the online option on/off do, and what are the possible reasons for taking a database offline and when would i need to do that thanks

M&M SSC-Insane Points: 21700 More actions · Answer 3

There was once when I had to restore a database.

Due to some reason, connections continued to persist on this database because of which I couldn't restore.

To overcome this, took the database offline and then performed the restore. 🙂

M&M

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

klineandking (1/19/2010)
Thank you for the reply i appreciate it i would like to know what does the online option on/off do, and what are the possible reasons for taking a database offline and when would i need to do that thanks

Here is something that may help to explain it:

http://blogs.techrepublic.com.com/datacenter/?p=249

Online and offline index rebuilds are not related to the database being offline. Rebuilding online allows for less intrusive behavior by the rebuild process so that users may continue to use the database. Rebuilding the index otherwise can cause heavy blocking on the tables while the indexes are rebuilt.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 5

And just so you know, rebuilding indexes using the "Online" option is only an option in the Enterprise and Developer's (Enterprise with very limited licensing) Editions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Suresh B. SSC-Insane Points: 22986 More actions · Answer 6

klineandking (1/19/2010)
Thank you for the reply i appreciate it i would like to know what does the online option on/off do, and what are the possible reasons for taking a database offline and when would i need to do that thanks

If you want to move database files from one drive to another, you need to take the database offline.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

mohammed moinudheen (1/19/2010)
There was once when I had to restore a database.
Due to some reason, connections continued to persist on this database because of which I couldn't restore.
To overcome this, took the database offline and then performed the restore. 🙂

Also,you could have gone with the option of putting the database in Single_User mode-->end your own connection --> put the database back to multi_user mode. This is to ensure no connections are active and you can do your restore.

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Richard M. SSCertifiable Points: 7401 More actions · Answer 8

Jeff Moden (1/19/2010)
And just so you know, rebuilding indexes using the "Online" option is only an option in the Enterprise and Developer's (Enterprise with very limited licensing) Editions.

..... and in order to be bale to do the online indexing, the tables cannot contain: XML index, Spatial index, Large object data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) in which case you need to do an offline reindexing (which does not mean taking the database off-line...)

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

g.graetz SSC Veteran Points: 254 More actions · Answer 9

Richard M. (1/20/2010)
Jeff Moden (1/19/2010)
And just so you know, rebuilding indexes using the "Online" option is only an option in the Enterprise and Developer's (Enterprise with very limited licensing) Editions.
..... and in order to be bale to do the online indexing, the tables cannot contain: XML index, Spatial index, Large object data type columns (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml) in which case you need to do an offline reindexing (which does not mean taking the database off-line...)

..and what we can do instead ?

we have navision 4.0 sp3 with many tables who have clustered indexes

and columns with data typ image etc.

we must accomplish an 24x7 manufacturing plant, there is no offline possible.:w00t:

thanks for an answer.

Richard M. SSCertifiable Points: 7401 More actions · Answer 10

If it is an 24x7 operation and there are LOB's, you are out of look doing them online. You will need to schedule downtime to reorganize/reindex those tables.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

ALI SQLDBA SSCrazy Points: 2121 More actions · Answer 11

Richard,

Even in our environment, we have the same situation. We have LOBS in our database and can't be rebuilt online. I am using the script in this link "http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/" which does rebuild/re-org according depending on the no.of pages and/or percentage of fragmentation.

I wonder How come MS has not included an option to Rebuild LOBS online. Anyways, the only fact that I am not sure with the above script is that, do we need any downtime for running the store proc in the script. I understand that, the script is flowing through each table by table in a database. because of this reason, I assume this may not require DB to be scheduled for a downtime. Not for sure. ::unsure:

Gurus, Please suggest. 🙂

Thanks

Alli

Richard M. SSCertifiable Points: 7401 More actions · Answer 12

Alli,

the script you mention should be erroring out on those tables that have LOB. We use a similar script and those tables are just "ignored" on our ONLINE run. We process those during our scheduled Downtimes...

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

ALI SQLDBA SSCrazy Points: 2121 More actions · Answer 13

Richard,

But when I ran this on my local machine, I didn't get any errors for LOBs. Pleases clarify

Thanks

Ali

Richard M. SSCertifiable Points: 7401 More actions · Answer 14

Ali,

If I run this for example on a table that has a LOB:

ALTER INDEX [TheIndexName] ON [PROD].dbo.[TheTableName]

REBUILD WITH (SORT_IN_TEMPDB = ON,ONLINE = ON)

In this case TheIndexName is a CLUSTERED Index on a table that has a field DetailText that is varchar(max), and I get this error back:

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'TheIndexName' because the index contains column 'DetailText' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

You probably don't have a CLUSTERED index on a table with any of the above field types and/or the index being reindexed is a NON-Clustered and does not contain one of those fields.

Or, on those tables that do have LOB's, the Fragmentation is less than 30 or the number of pages is less than 1000.

Does that make sense?

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]