Creating non-clustered index on large table fills transaction logs

Question

Using SQL Server 2016, I'm trying to create a non-clustered covering index on a table in my database that is of the structure int, bigint, int, varchar(20), varchar(4000). The size is ~13,241,928 rows.

create nonclustered index nix_TableName on Schema.TableName (
    Column2 asc, Column3, asc
) include (Column4, Column5) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The problem is the transaction logs fill up to quickly which sit on a drive partitioned for 50GB. The db is sitting on an availability group with 3 nodes, DW1, DW2, DW3.

What would be the most optimal/practical way of getting this index created without crashing? Can I throttle the index creation, like do it in batches? Are there any tricks I'm missing that I can include in my create nonclustered index statement?

I did find this documentation. I'm trying a few of these tips next.

There is already a clustered column-store index on the table.

Furthermore checking the activity monitor for the server I see that the process for the index creation is being suspended. With the wait_type being CXPACKET. I'm not specifying a MAX_DOP.

Answer 1

As far as I know the fact that the underlying table is a columnstore index isn't too relevant here. The linked documentation that you included in the question already gives you the standard fixes for your problem. Below is a list of possible fixes rated in

1. Increase your log file size. This will help you in the future if you ever need to drop and recreate the index for some reason.

2. Set the SORT_IN_TEMPDB option to ON. I didn't know this, but that option can help in cases when you have enough free space in tempdb.

3. Build the index with page compression. This can reduce the amount of data written to the transaction log, but that varchar(4000) column could cause issues.

4. Create a copy of the table's structure with the indexes that you need and insert data into it in batches. This can be difficult to pull off in production and will overall be inefficient but you can split up your index build into separate transactions this way. If something goes wrong just drop the temporary copy of the table that you made.

5. If the table is partitioned, you might be able to use partition switching to build the nonclustered index one partition at a time.

6. Temporarily change your recovery model to bulk-logged. This can cause all sorts of issues so make sure that you fully understand all of the ramifications of doing this.

Answer 2

I would go with the addition of storage for the log drive.

I guess you do maintenance on that database. Having more space may prevent the index rebuild to fail.

50Go is not that much for a really big DB. As storage is not that expensive, it may Worth the $ to save you from an incident with the DB down because of the log file full.

Thanks.