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.
asked Dec 11, 2017 at 04:01 PM in Default
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.