asked
Deleting rows in a 120 million columnstore indexed table
Hi Folks, I have an **OLTP** database **Events** with a single table **Eventlogs** having 20 columns with Eventid (int) , Logid (uniqueidentifier) and rest(timestampstart, timestampend etc ) as nvarchar. There is a primary key on Logid (uniqueidentifier). This is SQLl2012 Every night rows more than 3 weeks old are copied by an **SSIS** process to a reporting server into an archive table and database with the same name as the **OLTP** database. The rows are then deleted in the **OLTP** database. This reporting database is now a 366 GB table with 120 million rows holding data from 2012. There is a nonclustered columnstoreindex on id and 4 other columns and a unique nonclustered index on the logid(uniqueidentifier). The 1.6TB reporting disk is now almost full and I have been tasked to free up some disk space. There is a requirement to now keep only recent 2 years data in this archive table which means deleting around 70 million rows. What is the best way to proceed ? The data can be deleted in batches of 10 GB week . The Eventid seems to increment by 1 but am not sure it is the same across 120 million rows. Thank you for your help in advance.
@mail_sady Is it at all possible to use a partition switch to delete that many rows? There is an [answer to the similar question][1] (pre-columnstore indexes times though) by @Scot Hauder, please have a look at it. Partition switch makes the delete operation much faster because no data is moving anywhere, just meta is changed, so it might be worth it to consider dropping NCCI, deleting rows via partition switch and then creating NCCI once all is done. [Level 6 of the Stairway to Columnstore Indexes][2] by Hugo Kornelis discusses the consequences of deleting data in columnstore indexes in great detail. Of course, it goes without saying that the [entire stairway][3] (13 levels), just like anything else written by Hugo is very well worth thorough reading. [1]:
https://ask.sqlservercentral.com/questions/28252/deleting-10-million-records-from-a-database-table.html [2]:
http://www.sqlservercentral.com/articles/Stairway+Series/136174/ [3]:
http://www.sqlservercentral.com/stairway/121631/
Hi Oleg, I tried the partition method as described by Scot Hauder. I tried in on a sample of 2 million rows from the same table and it succeeded. I then tried it on the actual table , but during the step to create a clustered index ,the data file grew and filled up the disk and stopped. I had to delete the database and restore it from the backup. I guess now either I will have to move the data file to another disk to carry out the process or better still , restore the back up on another server and try it out( whether we have another non prod server with so much disk space is another issue). But thanks for your pointers, I have learnt a neat trick.
18 People are following this question.
