August 4, 2015 at 4:17 pm
Hello All
I have a new cluster (2 sync, 2 async) with about 50 databases going from 1 to 200gb ( all of the objects are compressed)
That at sql server 2012, sp1 CU7
I have several drives for logs with 200gb of space in there
I am having issues at rebuilding indexes on this env, ie, I have a table with the clustered index heavily fragmented (~80%), and the table has about 60gb of data, uncompressed that should be about 160gb
The index rebuild is creating a log file big enough as to consume all the space that I have for logs, and that is only 1 table, so for sure my old process to maintain indexes (ola.hallengren code) won't work on this scenario
can someone give me an idea of how this should be done in such env? or a guide of good practices?
Thanks in advance
August 4, 2015 at 6:22 pm
1) Why is the log getting filled up? Is it because one or more of your secondaries isn't keeping up with log receive/replay? If so, take those secondaries offline and reinialize them after your are done.
2) You could also reorg it in stages, keeping an on how backed up secondaries are and how full tlog is. Reorg is stoppable without issue.
3) I am having hard time understanding why rebuild is creating tlog bigger than table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 4, 2015 at 6:43 pm
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 small
I 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
August 4, 2015 at 8:43 pm
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?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 4, 2015 at 8:53 pm
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?
August 5, 2015 at 7:09 am
I pretty much always prefer the sort in tempdb option when tempdb will support the size and load of the operation.
You have done monitoring and proved that the tlog activity is being replayed completely to the secondaries in a timely fashion?
Kinda makes me twitchy that you are trying to get this done on a short time-frame. Bad things often can/do happen when HA/DR stuff is rushed and not well-thought-out and exercised. 🙁
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 5, 2015 at 7:11 am
Hi Ricardo,
I would try the sort in tempdb option as you pointed. Maybe if the index rebuilt is clustered, system my be also rebuilding the non-clustered indexes for this table too.
August 6, 2015 at 2:08 pm
as an update the sort in tempdb didn't work...
The latency between the primary and secondary replica (synchronous) is only 5ms
I am at a loss here, not sure what else should I look at
Another detail, there are about 100 dbs at the AG but only a bunch have considerable amount of use, the network works at 10gb...
Any ideas?
August 6, 2015 at 3:06 pm
ricardo_chicas (8/6/2015)
as an update the sort in tempdb didn't work...The latency between the primary and secondary replica (synchronous) is only 5ms
I am at a loss here, not sure what else should I look at
Another detail, there are about 100 dbs at the AG but only a bunch have considerable amount of use, the network works at 10gb...
Any ideas?
Further ideas by me would require me to be on the machine running a variety of scripts to check for root causes. Sadly this goes beyond regular forum support stuff.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply