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
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
CXPACKET. I'm not specifying a