asked
Delete data from sql table when running out of disk space?
Hello, I am trying to delete unnecessary data from a very large database table. I don't think the person who monitored this database before me ever cleaned it, so it needs to be done. The database is about 85GB large and I only have 37GB of free disk space, so when I run a query to look for unnecessary data, the drive runs out of disk space before the query can finish running. I was wondering if there is a way to search through data in smaller batches? Here is the query I am trying to run: DELETE FROM [StockTrace] WHERE [dRecQty] = 0 AND [dOrdQty] = 0 AND [dInvQty] = 0 AND [dRecvQty] = 0 Thank you, - Spangie
Check out this blog post by Aaron Bertrand --
http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
It kind of depends on your query. It sounds like you have a triangular join occurring that is causing log blowout which causes your disk to fill. We would need better information to assist with that query.
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
This sounds like one of those emergency situations that is not of your own doing and a little creativity is required. Here are some "emergency" options that you could try, I am sure that others will comment on their efficacy. 1. Note the free space size of the log file. Back up the database. Check free space in the log file. You may have recovered enough space to complete your work. Make sure you do not back up to the same disk 2. Take a full backup. Detach the database and move one of the files to a different disk. Re-attach, perform work. Detach and restore file to original location, reattach. I am sure that you will get this sorted; and when you do sit down work out the size your database needs to be to accomodate the growth for the next year ahead. Finally create a dummy empty database of say 5 % of total database size on each disk used to hold physical database files on the server. If you have space issues again you can retrieve space easily from SSMS.
I'm assuming that the issue lies with the transaction log for this particular database and not anything else. With that in mind: A database backup won't affect the transaction log file size. Only a LOG backup will do that.
http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/ Use caution. I would be very hesitant to use the detach/attach process. Once you detach it, SQL server no longer has control over it. If something happens to the file during the move your options are limited in trying to fix it. A backup is great but I would verify that the backup is sound by restoring it fully before trusting it. I would do this as a last resort. Are the drives fixed drives? Can you expand the drive? If you are in simple recovery model, you should be able to issue small delete batches (maybe 1000 rows at a time) until all of the required rows are complete. Once each batch is completed, the log file will roll around, thus not blowing out the drive. Given the post, I suspect that you are in FULL recovery. Even if your not in SIMPLE recovery, you can still issue small batches of deletes with transaction log backups between to help maintain the log file size. I would highly recommend the article mentioned by @DenisT. Hope this helps!
19 People are following this question.
