I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db. I've been trying to convert it to a columnstore table using the following commands:
insert Logs(Message) values ('Creating SRR table...'); select top 0 * into temp.SRR from dbo.SRR (nolock); create clustered columnstore index ix_SRR on temp.SRR with (online = off); create index ix_SRR_JobId on temp.SRR (JobId); insert Logs(Message) values('Populating SRR table...'); insert into temp.SRR with (tablock) select * from dbo.SRR (nolock); insert Logs(Message) values ('Switching out SRR table...'); alter schema old transfer dbo.SRR; alter schema dbo transfer temp.SRR; insert Logs(Message) values ('SRR table converted.'); if (select count_big(*) from old.SRR (nolock)) = (select count_big(*) from dbo.SRR (nolock)) begin drop table old.SRR; insert Logs(Message) values('Deleted old SRR table.'); end else begin insert Logs(Message) values('Row counts between old.SRR and dbo.SRR do not match; retaining old.SRR.'); end
This has worked for all of our other large reporting tables, but this one (after a good 30 hours of DTU time) consistently fails with the message:
Msg 40544, Level 17, State 2, Line 195 The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.
What can I do to make this work?