PRECAUTIONS TO BE TAKEN FOR DATABASE BEFORE REBUILDING INDEX

Question

PRECAUTIONS TO BE TAKEN FOR DATABASE BEFORE REBUILDING INDEX

gajananhatkar

Mr or Mrs. 500

Points: 541
More actions
June 25, 2011 at 2:17 pm

#239870

hi,
before rebuilding index what precautions to be taken

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

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

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 1

In most cases: none. A reindex won't damage or loose any data if certain steps are not done first. It just works.

That said, there is a penalty to the system during the reindex process. An offline reindex will harshly block access during the rebuild, in exchange for the rebuild being quicker. Smaller tables may not be any problem at all, while larger tables could be noticable to users. Therefore, this is designed to be done during maintenance hours when users are not in the system.

If your version of SQL Server supports Online reindexing, then SQL Server will not be so harsh during the reindex, in exchange for the process taking a lot longer. This is needed by 24x7 shops that do not have a maintenance window where the system can be taken offline at regular times.

There are other impacts that a reindex has on a system, but the above are the main ones. Search this site for forum posts and articles that talk in more detail about this. For example, when to defrag indexes and how good of a job does defragging do, are additional topics related to what you are asking.

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph

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

Make sure you have enough log space. Other than that, pretty much nothing

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 3

Next to checking if you have enough log space, also check if your log file is in autogrowth, if it is then check if the disk where the log file is located has enough space.

My blog

aatishpatel SSC Veteran Points: 232 More actions · Answer 4

I would change the database recovery to simple or bulk logged.

Check if i have lot of space on the drives.

Thank you.
Aatish Patel

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

aatishpatel (6/27/2011)
I would change the database recovery to simple or bulk logged.

Bulk-logged unless you want to break the log chain and leave the DB open to major data loss risks until a backup (full or diff) can be taken.

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

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

aatishpatel (6/27/2011)
I would change the database recovery to simple or bulk logged.

I would not do that unless you have a good reason and understand the tradeoffs; it's not going to hurt (other than using significant log space) any more than any other major operation.

Most of my largest tables are in Full recovery mode databases; they get reindexed just like everything else.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

aatishpatel (6/27/2011)
I would change the database recovery to simple or bulk logged.

[font="Arial Black"]WHY???[/font]

--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)

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 8

consider Backup also

My Blog
www.aureus-salah.com

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

Syed Jahanzaib Bin hassan (6/28/2011)
consider Backup also

Before rebuilding an index?

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