September 22, 2011 at 3:43 pm
tables/indexes are heavy fragementated on production instance, what about the hot standby instance ( it's setup by log shipping every 15 minutes ) ?
Tables are big, will take long time to defrag, it could cut the downtime if the hot standby tables are good and switch back after .
ddfg
September 22, 2011 at 4:51 pm
The secondary server should also be fragmented like the primary. Your updates, deletes, inserts are being transfered via logshipping to the secondary. Page splits that occur in primary should also occur in your secondary due to that.
Check the frag level on the secondary and you should be able to see that.
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
September 22, 2011 at 5:11 pm
Not able to check hot standby 'cause it's in unrecovery mood, but thanks for the info, looks like we don't have choice but shutdown the application server and rebuild indexes on production instance, what about hot standby ? Do i have to rebuild standby dbs from scratch again :
1) rebuild indexes on prod;
2) full backup;
3) copy over to standby and restore it in unrecovery;
4) resume log shipping ;
I guess the TLog will be boomed during the rebuild process, if i change to "Simple", i have to rebuild hot standby dbs anyway, right ?
Thanks a lot !
ddfg
September 22, 2011 at 5:16 pm
Rebuilding your indexes on the primary should rebuild via logshipping when you rebuild on the primary.
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
September 22, 2011 at 7:57 pm
Jason is correct. Rebuilding on the primary will rebuild on the secondary.
There's no magic in log shipping. It backs up the transaction log and restores it. You can configure log shipping to restore with STANDBY instead of NORECOVERY, which would allow you to query the secondary.
Index rebuilds are logged, so they are backed up in the log backup (making that log backup larger). That same operation is replayed on the secondary.
November 29, 2012 at 12:25 pm
Consider switching to BULK LOGGED RECOVERY MODE before Rebuilding Indexes. This will minimize the growth of the log file. You can later switch back to the FULL RECOVERY MODE
November 29, 2012 at 1:11 pm
Just be aware that the next transaction log backup will still need to include the extents that were changed during the index rebuild while in BULK LOGGED so it won't be any smaller. Log Shipping is one reason to use a smart index rebuild process. I have seen cases where a DBA setup a database maintenance wizard task which rebuilt indexes that were not fragmented and caused the log shipping to fall way behind when copying the transaction log backups across the network. Replacing that with a process to only rebuild the fragmented indexes fixed the problem.
November 29, 2012 at 1:15 pm
Please note: year old thread.
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
November 29, 2012 at 2:23 pm
Old but Gold Topic thread. Still on top on google search. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply