Follow

I have deleted a lot of data from the database, but the disk space has not changed

PROBLEM
After deleting data from the database, the MDF file on disk is still the same size

CAUSE
In MS SQL server, when you delete data from the database, the database file (.MDF) does not automatically become smaller. This is by design. Increasing the size of the database then shrinking all the time is inefficient.

In an ideal world - you'd create your DB with plenty of free space to grow into. I call this "Right Sizing" your database. You would allow this free space to be there and not strive to give it back and keep your total size right at your used size, because your database will eventually grow again. Then you would need to shrink again and you'll be stuck in this pattern of useless shrinks followed by growths - and you'll be increasing  index fragmentation.

WORKAROUND / RESOLUTION
Sometimes you need to shrink the database - this is typically if you have run out of disk space. If you have a large database, and have just freed significant space that you don't expect to grow back into it, then it is okay to consider shrinking.
The shrink operation can be time consuming so you'd want to plan it for a time where you can pay that price of a shrink running.
Importantly, after a database shrink, the indexes will be heavily fragmented and effectively useless.
Once the shrink is complete, you must rebuild the indexes.

Kaseya R8 and above will do this nightly, but there is also a link on the System Tab -> Configure page called "Defrag Database". Again, this will take time, but it will rebuild and defrag the indexes in the database and restore performance.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.