A
A
Andrey-22021-02-01 10:30:12
SQL Server
Andrey-2, 2021-02-01 10:30:12

What is the best thing to do when deleting a large number of records in a database table?

Good afternoon.
The question is probably hackneyed, but you can help with advice.
There is a table with sessions in which there are a lot of records (~ 10,000,000), no one cleaned it before, and now there is a problem with the size of the database.
I wanted to delete it, but I remembered that when deleting a large number of records, there may be problems (defragmentation, etc.). Not everything needs to be deleted, only older than 1 year (i.e. a lot)

What is the best thing to do?
1) Delete using DELETE (how then to bring it back to normal if queries are then slow, and performance is critical because there are many calls to it)
2) Move records for the last year to a temporary table, delete this table and create it again (as be with connections)
3) Overwrite?
4) Leave as is (Won't help, disk space is limited)

З.ы. How do they work with such a large number of records?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Andrey Skorzhinsky, 2021-02-01
@Andrey-2

1 Make a DB buckup
2 Delete unnecessary records (only delete here, because truncate destroys all records, but quickly) Sometimes you have to do it in chunks, in separate transactions. Depends on the load on the database.
3 Update statistics

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question