0

Good Afternoon,

We have a database that wasn't been an archive anytime. The database has grown tremendously and we are running short of space. Hence there is a need to do a bulk delete and free space. Based on my research, in order to free up space, I have 2 options: shrink DB or shrink files. The shrink DB is a bad option because it ruins the fragmentation. Can anyone guide me on how to perform a bulk delete and free space? Also, we are planning to implement this process on a monthly schedule job.

Based on the discussion, I want to clarify 3 things:

  1. Bulk Delete Without Locking The Table
  2. Reclaim Free Space
  3. Schedule Monthly Job

Looking forward to expert opinion.

Improve this question

3 Answers 3

Reset to default
0

To get rid of a lot of rows there are various options, hopefully the brief descriptions below will help you choose which one might be a "fit".

  1. Use Truncate Table - this removes all the rows in the table by deallocating the extents - and is therefore a fast operation

  2. Partition your tables and then use a partition switch to switch out the oldest partition once you don't need it anymore - assumes your partitioning design will group rows of similar age together into the same partition.

  3. Use an ordinary delete but do it many times, to avoid Lock Escalation and massive blocking problems, can still cause problems with transaction log growth - see the linked earlier answer

Delete rows older than x days without locking table

Improve this answer
6
  • Morris - Thanks for the detail. The options you suggest, will that release free space?
    – rushabh138
    Mar 8, 2021 at 20:53
  • Hi - you want to release the space back from the database to the operating system ? Then you will have to shrink the file(s), this can cause some fragmentation but it is not the end of the world. However you can only shrink the file if there is empty space so you will presumably need to remove some rows - hence my answer. The command for shrink is DBCC SHRINKFILE
    – Stephen Morris - Mo64
    Mar 8, 2021 at 21:01
  • Morris: To conclude, I need to perform the following steps: 1. Drop Index 2. Delete [n] number Rows based on conditions 3. DBCC Shrinkfil <file group name> 4. Re-create Index.
    – rushabh138
    Mar 8, 2021 at 21:14
  • what sort of index are you talking about ?
    – Stephen Morris - Mo64
    Mar 8, 2021 at 21:37
  • Both, Cluster and Non-cluster index.
    – rushabh138
    Mar 8, 2021 at 22:12
2

I suggest you take a step back and look at the overall picture:

You remove some data, and by next month you have added more data and yet again remove old data.

Let us play with some numbers:

Say you have 100 GB data file at end of month. You remove 50 GB. So you shrink the file and gain 50 GB disk. What usage do you have for these 50 GB? These 50 GB will be used over the month, so at end of next month the file is again 100 GB. You have temporarily gained 50 GB disk space, but you can't use this disk space - if you do you end up in a situation where the database is full when it need to grow.

And on top of that you have the downsides of shrinking.

  • It takes time.

  • It fragments you indexes. Sure, index fragmentation isn't as bad as when we had spinning disks but it still come with some cost (mainly read-ahead read crap into memory and you pollute memory with stuff you don't need, pushing useful stuff out of memory). I've elaborated and did some measurements regarding fragmentation here. So, frag doesn't hurt that much, but even say 20% or 10% slowdown can be relevant - so why fragment the indexes when it doesn't buy you anything?

  • And, of course, if you are in full recovery, the data that was moved by the shrink is now logged, requiring a large ldf file and also making the following log backup huge.

  • And all the other downsides of doing shrink - which has been elaborated in many places (mine is found here).

Bottom line is that you should consider these 100 GB as a part of the cost for this application/database and get financial approval for this. :-)

Improve this answer
2
  • what do you recommend as an appropriate strategy for archival and delete of old data then?
    – rushabh138
    Mar 9, 2021 at 12:28
  • I didn't address delete or archival of old data above. I only addressed whether or not it can be a good idea to shrink after getting rid of old data. As for how to do the "get rid of old data" thing, I pretty much concur with Stephen Morris' answer. But we are now addressing two very different things. One is whether you should reclaim disk space. The other is how to remove rows from a table in a neat way. Two separate discussions.
    – Tibor Karaszi
    Mar 9, 2021 at 13:20
0

Shrink isn't evil; it should just not be routine.

Do the shrink after the first DELETE only. Then allow the database to grow back to the monthly high-water mark and remain there.

Improve this answer
2
  • Browne - how to regain fragmentation after shrink?
    – rushabh138
    Mar 8, 2021 at 20:55
  • Just don't worry about it. Fragmentation is typically not harmful. You could always rebuild indexes if you're worried about it. That will also make sure you have some free space in the database.
    – David Browne - Microsoft
    Mar 8, 2021 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.