How to perform bulk delete and release free space? 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:

Bulk Delete Without Locking The Table
Reclaim Free Space
Schedule Monthly Job

Looking forward to expert opinion.
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. :-)