Well, I am just saying the behavior I do see in there
It is not production yet, but that will be next week and I do need a proper maintenance plan that do not involve bringing down our backups...
the logs are small with the regular transnational operations, the rebuild and yes, I am 100% sure I am not doing a reorg, is what is making the logs to get large since at that moment I was the only one into that server...
the 200gb log file happened rebuilding the index of a 60gb table, I've been trying at several tables on different dbs and the result is always the same,
the rebuild is using this code:
ALTER INDEX idx_xxx ON [scf].[tb_table1] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = off, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, data_compression=page)
I am wondering if I should be using the sort in tempdb option
TheSQLGuru (8/4/2015)
ricardo_chicas (8/4/2015)
This is not in production yet so I was testing the maintenance jobs, the replicas are getting the logs properly and on regular use the backup strategy is able to keeping them smallI am not sure if I can disable our disaster recovery solution for a number of hours until the rebuilds are done, that is something that we will need to do on a regular basis
It was also a surprise, it may be because of the parameters of the rebuild I do not know, even rebuilding a small table, lets say 1gb, creates a 10gb log
I guarantee you that you are not creating a 10GB log rebuilding the clustered index on a 1GB table, no matter how fragmented it is. Perhaps you are doing a REORG instead a rebuild, which can create massive tlog activity on it's page swaps from heavily fragmented indexes.
If your stuff isn't in production, what's the problem? You should be able to fix the index problem and then rebuild the AG stuff.
Also, you said you were filling up 200GB of tlog space but now you say tlogs are flushing and keeping small. Can't be both. Did I miss something?