snippetsqlMinor
How to delete large amount of data from a sql table?
Viewed 0 times
deleteamountsqllargehowfromdatatable
Problem
We ran out of disk space and we have a table with about 10million records.
We'd like to delete the old records which are about 2million.
What are the impact of just deleting these using a simple DELETE command on production?
What's the best way to do this?
We'd like to delete the old records which are about 2million.
What are the impact of just deleting these using a simple DELETE command on production?
What's the best way to do this?
Solution
The impact will depend on a number of things including your dependencies and indexes on the table. If there are few dependencies (i.e. foreign keys) and few indexes then the delete will go faster. In any case it is best to do this type of operation "off peak" hours when there are the fewest number of users on the system.
If you're in the pinch right now then you'll have to perform the delete now. The system will be a bit slower than usual while the delete is taking place.
You should also understand that due to the way database and log files grow in SQL Server you will not suddenly have a bunch of disk space to work with. To free up more disk space you need to look into other things (in the short term) such as log truncation and db shrinking. Then, figure out a better long-term disk solution.
If you're in the pinch right now then you'll have to perform the delete now. The system will be a bit slower than usual while the delete is taking place.
You should also understand that due to the way database and log files grow in SQL Server you will not suddenly have a bunch of disk space to work with. To free up more disk space you need to look into other things (in the short term) such as log truncation and db shrinking. Then, figure out a better long-term disk solution.
Context
StackExchange Database Administrators Q#30675, answer score: 4
Revisions (0)
No revisions yet.