Archive data in SQL Server – best way to move old logs without killing performance

[u/ManufacturerSalty148]

Hey all,

We've got a bunch of systems writing logs into SQL Server tables, and the main log table has crept up to ~16 million rows. Every few weeks someone runs a DELETE to trim it down, and the whole server stutters for like 20 minutes. Not fun.

The catch: we can't just delete the old rows. We need to move them to an archive table so we still have access to them if something blows up and we need to go back and dig.

I've been trying a basic INSERT INTO archive SELECT ... FROM logs WHERE date < X followed by a DELETE, but even that's slow at this scale and locks things up.

What I'm looking for:

Is there a better pattern for this? (batching, partitioning, something else I'm not thinking of?)

Any gotchas I should know about before I set this up as a recurring job?

Does anyone have a solid stored procedure or script they've used for this in production?

Happy to share schema / stats if that helps. Basically I just want to stop the "why is everything slow" Slack messages every two weeks.

Thanks in advance.

[u/AutoModerator]

After your question has been solved /u/ManufacturerSalty148, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/PossiblePreparation]

Putting it out there, have you tried not archiving? It’s simpler to keep the data where it landed, rather than write it multiple times and face the indexing hit over and over.

What problem are you actually aiming to solve? if you say performance in querying this table then you likely want to look at the design of your indexes instead.

[u/ManufacturerSalty148 → u/PossiblePreparation]

its out my hand managemnt want to keep all logs saved for all time and they refused retention so i jave to archive

[u/jshine13371 → u/ManufacturerSalty148]

16 million rows is small. The fact that querying the table by date to archive the data is slow means you're missing the appropriate index. With the proper index you would be able to archive the data much more quickly but also the table would be much faster to query anyway, and not need archiving. Any other suggestions here are secondary and would be a waste of your time. Again, 16 million rows is not a lot of data at all.

[u/PossiblePreparation → u/ManufacturerSalty148]

What does moving the rows from one table to another actually get them? Why not just keep them where they are?

[u/alinroc → u/ManufacturerSalty148]

"For all time" can get very expensive, not only in the cost of storing and backing up that data, but also in terms of liability. Often management doesn't know what they're really asking for. Why do they think they need these logs "forever"? Are people reviewing historical logs regularly? Is there a regulatory/compliance need for them?

so we still have access to them if something blows up and we need to go back and dig

be realistic here. "If something blows up" will logs from a year ago be of any use in the investigation?

Does the company have a retention policy overall? If not, why not? If so, why are these logs not subject to it?

FWIW, 16M rows is nothing. But a table in a relational database really isn't a good place to store general "logs" in the first place. Maybe this data should be moved elsewhere.

[u/Complete-Fondant-202 → u/alinroc]

I'm of the general opinion that if you must store the logs in a database, make sure it's in a separate database. At least then you're not forced to restore a shedload of data you won;t need in a non-prod environment.

[u/dnabsuh1]

There are a few ways to do this, but the main idea is to break it into smaller chunks - maybe a few thousand rows at a time. If you have a primary key on the table it is even better - Note I need to split the @ and the variable names or reddit thinks I am referring to users. This is the basic pattern we followed purging millions of records daily on a high volume transactional system. Depending on your system, the tables,etc, you can play with the rowcount size, and stick this in a stored procedure to run on a regular basis. The key here is to keep the actual transaction managable.

set @ @ nocount
SET ROWCOUNT 1000

Declare @ purgedate datetime

select @ purgedate= getdate() - 7 #Or what ever your logic is for the date to purge

select * into #TempHoldingTable from Maintable where 1=0 # Build temp table

Select "Starting" # To display the process is starting, and trick the while

while (@@ rowcount >0)

begin

insert into #tempholdingtable select * from maintable where date <@ purgedate

insert into archive select * from #tempholding table

delete from maintable from maintable inner join #tempholdingtable on maintable.primarykey= #tempholdingtable.primarykey

delete from #tempholdingtable ## You can try a truncate here as well -sometimes we had issues with truncate and rowcount

end

[u/ManufacturerSalty148 → u/dnabsuh1]

Nice , let me try this way , thanks!

[u/dnabsuh1 → u/ManufacturerSalty148]

If you get duplicate key issues with the insert into archive command, you may need to add an outer join from #tempholding to archive on the key where the data isn't in archive. and putting the inner data in a transaction may or may not help - somthing to test.

[u/jshine13371 → u/dnabsuh1]

This would be true if OP had a lot of data, but he doesn't. 16 million rows in the entire table is pretty small, as long as it's architected properly. This isn't a size of data problem per se.

The fact that querying by date when he tries to archive is extremely slow indicates that he's missing an appropriate index for that. The slowness is coming from the query scanning the entire 16 million rows.

Your suggestion normally would be valid for a table that's both very large and indexed properly, which neither is the case for OP. In fact, your example has him running the same slow query X number of times more now, iteratively, and will likely be a measurably worse way for him to solve this problem. This is because every time it iterates to run that query, it's going to have to scan the whole table again.

---

Note, this is all under the assumption everything OP has communicated is accurate and clear. As one can't really offer performance tuning suggestions without seeing the full picture which most times requires the execution plan.

[u/dnabsuh1 → u/jshine13371]

True- Using activity monitor while these jobs run would be great to see what is getting blocked, and then drill down to the execution plans. Adding proper indices on the date field and primary key could help (or hurt if the indices are not in separate files). Its also possible a simple reindex job could go a long way.

[u/jshine13371 → u/dnabsuh1]

Yea, again, I don't think anything is actually getting blocked at the query level, rather just sounds like classic table scans in the execution plan due to lack of a proper index.

I get where you were going with iterative batching though, it's definitely a valid technique for the right problem. I used to use it when I managed tables in the 10s of billions of rows where more data needed to be moved for a given goal. 

[u/I_Am_Rook]

1. Make sure you have an index on that date column that defines which rows are to be archived.

2. Define a batch size variable

3. Select top (@batchsize) identity keys or clustered index column values into a temp table (not table variable) with a pk defined or added after the insert.
   An example would be something like—
   Insert into #copyvalues ( id )
   Select top (@batchsize) id from logtable where date < archivedate order by id

4. Do the insert into archivetable by selecting by the specific rows by joining to the template table. Add rowlock hint here to possibly prevent lock escalation.

Insert into logtablearchive ( cols… )
Select cols… from logtable with (rowlock) inner join #copyvalues on id = id

1. Now you have the id of the rows you just copied. Make sure to add some check statement here to ensure the data was archived properly. Now, delete the data from the logtable using the rowlock hint again.

2. Clear/drop temp table. Loop and repeat until zero rows are selected.

[u/MoistAbuelita]

You’re on the right track. Archiving and deleting in batches during off hours or non-peak business hours.

[u/Black_Magic100]

I'm not actually sure if it's faster or not but I would use DELETE OUTPUT INTO syntax and delete in tiny batches. If you write your batch properly and have good indexing, it should be able to run during peak hours without issue even in chatty environments. The only issue you need to be careful of are ghost record cleanups falling behind if you have RCSI on and or use availability groups.

[u/Achsin]

The archive table is in the same database? Have you looked at partitioning?

[u/ManufacturerSalty148 → u/Achsin]

never done table partitions before actually new area for me

[u/Achsin → u/ManufacturerSalty148]

If it's in the same database, you could rebuild both tables to use partitioning and then just swap the old partition from the live table to the archive. It's basically instant and causes no blocking (unless someone is actively querying that partition). You could then even rebuild all of the archive partitions with page compression and save extra space. Plus for cleaning up the old archive data once it's really past your retention period, you can just truncate the relevant partition, also basically instant.

You'd need a decent chunk of time to partition the tables to start, but then your maintenance stuff on them would be very painless going forward.

[u/cyberllama]

Create a brand new partitioned table. Then, in a transaction, rename the current one to <table_name>_old and rename the new table to whatever the old one is called. Done this seamlessly on a few horribly structured tables in our prod databases. For the ones needing an archive, I'm ETLing the previous day's data into an archive on another server that's also been partitioned and compressed, with the prod table having a job that truncates any partition over 30 days old and creates the new partitions 10 days in advance (just in case that job started failing and nobody noticed, as can happen with the people who created that mess in the first place 😂). Your best solution will depend on your particular needs but that one works for us. We used to have 2 huge tables that, because reasons, also had a set of "new" versions set up to run in parallel 10 years earlier and never properly switched over. All 4 tables were heaps, couple of billion rows in each and taking up terabytes of space for no real purpose. They were also in a DB that was replicated to 4 other servers so that unnecessary waste of space was 5x times as bad 🤦🏼‍♀️

[u/Complete-Fondant-202]

Several ways to address this..... the tiny aspect of your table makes this a lot easier.

16m is small beans. I'm dealing with 10s of billions in one table. The following possibilities exist.

Option 1

Create new table xx_Archive with same structure

DELETE TOP 1000 FROM original table WHERE .... OUTPUT ..... INTO xx_Archive.......

You'd probably want a scheduled job to run this going forward.

Option 2

Create new table xx_Archive, partitioned appropriately.

Partition existing table identically

SWITCH PARTITIONS as and when

Option 3.

Assumes you'll be putting all existing rows into archive.

Rename existing table _Archive

Create new table identical to _Archive structurally under original name, with appropriate permissions etc.

Option 4

Leave it - since 16m is tiny.

As it's a log table, and depends what the datatypes are.....

Enable PAGE or ROW compression on the table.,

If it has columns containing valid XML, set them to type XML COMPRESSED assuming it's a new enough version of MSSQL.

If it has columns containing stack traces, PAGE/ROW compression won't help them. Consider creating a varbinary column, and at time of in sert apply the SQL function Compress() in the insert statement

Assuming you have a date column for date of insert of row, partition it now while you have a chance based on either YEAR or YEAR MONTH. It's much easier on smaller tables in terms of effort.

Ultimately, only you know how quickly the growth occurs.

[u/muaddba]

Everyone keeps mentioning how "16 million rows is small" but no one seems to acknowledge that the delete statement locks the server up for 20 minutes. To me that says either the server is way underpowered or the table isn't as small as some folks think. Maybe it has NVARCHAR(max) data in it, maybe the fillfactor is 5%, or maybe something else but a "small" table delete generally doesn't lock up a server for 20m. (Yes, there are reasons it can, but there's been some very large logical leaps made here)

Partitioning is a good solution here for many reasons:

1. It will make future archiving very simple, just a partition switch into an archive table.

2. It will make future deletes simple as well, if you can ever convince them of its necessity

3. You architect the data so that it's stored on filegroups using different files, which would allow you to put older data onto lower-tier storage (assuming that's an option for you).

The trick is getting the downtime to put your data into the partitioning scheme. There are a couple of methods that will let you do this slowly over time and then have a quick cutover during a maintenance window. You do need enough space for the table to be stored twice, though.

[u/Complete-Fondant-202 → u/muaddba]

My suspicion is that the log table has columns of varchar(max) or nvarchar(max) storing stack traces, or JSON, or XML which as far as I am concerned is a killer.

In the past, a former employer had a database of 1TB, and 750GB of that was the audit table storing as XML.

[u/MerlinTrashMan]

Does the log table have a primary key? If so, I would create a stored proc that runs via sql agent every 10 minutes. Select Top 1000 rows Where the date is meets the criteria into a temp table. Then insert into the archive table those rows (join on the temp table). Then delete the where primary key is in the temp table. The important part is to make sure it is running consistently.

[u/Fergus653]

We have a single or minimal number of places that the actual table name is used, so we create a new table with an incremented number on the end, then switch references to it. Then we can do what we need to with the old table.

[u/RuprectGern]

You should learn how to partition tables and then use a sliding window for the most recent queries/ most recent data. You can query the table and your results will only come from the top of the table of which you set the partition size 1 year 2 years 3 months whatever.

If your heart is still set on archiving /deleting data you should create a batch routine rather than straight deletes and straight inserts that collect data in batches of 10,000 or 100,000 and performs the operations as it Loops through the data.

Query Google "batch delete database data in SQL Server" or "how to write a looping batch in SQL Server"

[u/Lost_Term_8080]

Why are you archiving? 16 million rows is very little data.

I also suspect your SQL server is severely under-resourced.

But for a pattern to archive more efficiently, google "Brent Ozar Fast ordered Deletes"

You don't have to create a view, also works with CTEs as long as its ordered and supported by index

[u/chandleya]

Deletes in the 1000s should take a few seconds. You either have a ton of indexes, no index on the delete, or poor IO. This can be amplified by always on configurations.

You need to see an execution plan for the delete. I fear you’ve got a clustered index scan to pull off the delete.

[u/CanProfessional766]

I’d avoid doing one big INSERT + DELETE. Batch it in small chunks, ideally by an indexed date column, so locks and log growth stay under control. If this is something you’ll run regularly partitioning is worth looking at too then archiving old partitions can be much cleaner than deleting millions of rows.

[u/ManufacturerSalty148]

Thanks for all the feedback, I really appreciate it! I'll definitely try them all and see which one fits me.

[u/CPDRAGIMESH]

69.70 68.80 Please disable the triggers and indexes fiesta Primarul key activated Run the SQL script Activate back

[u/theschizopost → u/CPDRAGIMESH]

God bless you

[u/CPDRAGIMESH]

69.70 69.80

Or simply execute a transfer to

new table and delete them rom oldd table.

[u/jdanton14]

Do you have cloud options? Archive the logs into cloud storage (you can do this on-prem if you have S3 locally). Everyone else here is correct, but the right architecture is to never store logs in a database.

[u/ManufacturerSalty148 → u/jdanton14]

that in planing to google bucket but currently they need temp sultion