web stats
Mirth Community - View Single Post - Mirth 3.2 - SQLserver - huge database
View Single Post
  #5  
Old 03-17-2016, 06:51 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

In SQL, and pretty much all databases, just because your prune/delete/remove records, doesn't mean that space is recovered. The data is gone, but the database space allocated for the records is still there unless you do something.

In an ideal world - you'd create your DB with plenty of free space to grow into. 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.. Why? Because your database will eventually grow again.. Then you'll shrink again.. And you'll be stuck in this horrible pattern of useless shrinks followed by growths - and the entire time you'll be increasing your index fragmentation.


However, if you're positive you want to reduce the data file size, then you can try to run a DBB ShrinkFile on it, but be aware:

It causes massive index fragmentation.

Affects performance WHILE it's running and AFTER it's run (one exception - the WITH TRUNCATEONLY option, if you're just removing free space at the end of the file).

The only way to remove index fragmentation without causing data file growth again is to use DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE.
Reply With Quote