Rebuild Index - Loghshipping

Question

Rebuild Index - Loghshipping

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
November 3, 2010 at 10:24 pm

#149180

If i rebuild an index on the source database which is logshipped , will the rebuilt index be done on target database also?

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Yes, the operation is fully logged, so the indexes on the secondary server will be rebuilt automatically.

However, we don’t recommend that you rebuild indexes often for a log shipping scenario since rebuilding indexes may cause huge logs which may cause a performance issue or disk space issue. You could first consider update statistics, then based on the fragmentation level, you could use reorganize indexes (a few fragmentation) and rebuild indexes (heavy fragmentation).

http://technet.microsoft.com/en-us/library/ms189858.aspx

http://msdn.microsoft.com/en-us/library/ms187348.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

but indexes are used on queries based on statistics and statistics are stored inside master database of local server. Am i missing anything here?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

sqldba_icon (11/3/2010)
but indexes are used on queries based on statistics and statistics are stored inside master database of local server. Am i missing anything here?

In simple language i would say , what ever you will make changes on primary server it will be logged into logs and these logs will be ship to destination server so all changes will be replicated to there.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 4

thanks Bhuvesh,but will the indexes behave the same way as in primary since statistics are local to the server?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Our Secondary server is more often in recovering or stand by state and we made it by restoring the full back up of primary one. after that sequence Tlogs from primary So it means we will get same statistics on both sides.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

I guess index statistics are stored on the database level e.g.

object: sys.indexes

and

Dynamic view: sys.dm_db_index_physical_stats

Index is nothing but bunch of data pages(cluster index) and index pages(non-cluster index).

You are talking about the statistics on the master database; can you please provide more detail what kind of statistics are you refering in the master database?

Hope above explanation clarify your doubt?

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

thanks for replies. I am talking about statistics of the database, as far as i know statistics of each database are stored in local master database. I could be wrong though. Can someone clarify where are the statistics of the user databases stored?

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

sqldba_icon (11/8/2010)
thanks for replies. I am talking about statistics of the database, as far as i know statistics of each database are stored in local master database. I could be wrong though. Can someone clarify where are the statistics of the user databases stored?

Nope,statistics are stored within the database.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

azad maratha Default port Points: 1468 More actions · Answer 9

in the end ,

if u rebuild indexes on primary,same thing will happen in secondary.

what u can do is depending upon the fragmentation levels decide which indexes to rebuilt and which to reorganize.

Azad R Sale MCITP(2008)