Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
A delete of 2 million records will hit the transaction log for 2 million and if you are out of disk space it simply will not complete.
Is logging set to simple?
Fist look for relief some where else.
Cache, temp, ...
Can you move backup(s) to another box?
Are there tables you plain don't need?
Are there tables you can truncate (does not hit transaction log).
You are going to need to delete a few records at a time.
I would start with 1,000 then 2,000 and double up until you get to 100,000 at a time.
This might take a long time depending on indexes and such. What I have done in the past is to delete maybe 50,000 or 100,000 at a time. This will keep you up and running while minimizing your slowdowns.
Also, I ran into problems doing this programmatically. PHP, for example will issue the commands buy actual deletes will be cached by the server. If you need just a little space to keep things going, this approach will get you there with the least amount of system lag.
