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.
I have deleted a lot of data from the database, but the disk space has not changed
Have more questions?
Was this article helpful?
Provide feedback for the Documentation team!
Browse this section
- "Oops. Something went wrong" error message.
- "Unable to validate license License file Lc.txt was not created" message when running kinstall.exe
- 9.4 Schema installation fails on non-English locals (OS/SQL language)
- All my SaaS agents show as suspended
- Auto Deploy uses wrong agent icon
- AWS API endpoints TLS 1.2 requirement
- Can I backup the Kaseya database to a mapped drive?
- Can I install add-on modules through policies?
- Can I install Kaseya VSA / KNM when SQL is running on a non-standard port
- Can I install PowerShell 4 on the Kaseya server?
- See more