CODEWITHSUNDEEP
sqlsql-server
albier naguib
albier naguib

Reputation: 43

Insert sql rows instead of deleted rows will make database size increase or still the same

I have a large database its MDF file nearly 190 GB. When I delete a large number of old rows the MDF size doesn't get reduced. What will happen when insert new records into my database? Will the size of the data-files increase or does the new records fill the available / free space created by the deleted rows. I can't shrink the database or copy it into a new empty one. This is a production database and doing any of these operations would require me to stop the website which is not feasible for me. Should I acquire more disk space from my hosting provider???

Upvotes: 3

Views: 411

Answers (1)

S3S
S3S

Reputation: 25112

What will happen when insert new records into my database? Will the size of the data-files increase or does the new records fill the available / free space created by the deleted rows

It will fill the free space.

I can't shrink the database

You shouldn't, either. At most, you should ONLY use SHRINKFILE. However, this shouldn't be something you do often either. It should only be done when (by accident) your data-files have grown abnormally large. Most databases continue to grow as time passes since most aren't purged of any data. Thus, it's common to require more space (see below)

Should I acquire more disk space from my hosting provider?

As you insert more data, and are unable to delete any "old" data, then yes, you would need more disk space. Note that though your datafiles are a certain size, that doesn't mean they are full. You can check the disk free space via a report. This blog and others explain the difference between Unallocated, Unused, and Free Space

Upvotes: 3

Related Questions