asked
rebuilding indexes with always on
HI All, I have an always on setup for 1 database . recently i ran an index rebuild job and i specifically set the sort to occur in the database log file. my assumption was that when the indexes were rebuild on prod they would occur on the secondary server in the db log file also. however what seems to have occurred is that the TEMPDB grew to a large size on the secondary server and i am wondering is that coincidence or does the rebuilt indexes get sorted in the secondary TEMPDB rather than the db log file? Thanks in advance,
I guess you've used the "SORT IN TEMPDB" option? Which means temporary sort/recreate operations are not using space in the datafile being processed, but in tempdb. So you should see this behaviour also on the primary replica. AlwaysOn is based on transaction replication (like mirroring). So every "REBUILD INDEX" statement is also executed on every replica
HI Winfred, the quick answer is no. the sort is happening in main database on primary. however there was a disabled optimization job on the standby server which had the sort in TEMPDB enabled from a previous run. would that override the index rebuild from the primary side?
Don't think so. You can't write on secondary replicas. Are you sure this is happening on a database which is in an Availability group?
HI Wilfred, i know you can't write but an index rebuild on the primary which is performed within the db rather than the TEMPDB should be replicated to the standby. the peculiar thing is that the TEMPDB grows on the standby. i have altered the disabled job on the standby to see if the settings in that have any impact.
18 People are following this question.
