Space is not reclaimed in distrubution database

Question

Post reply

Space is not reclaimed in distrubution database

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 1

How about running this query

use distribution -- assuming this is your distribution db name

select name, total_space_MB=size/128.0, Used_space_MB= fileproperty(name, 'spaceused')/128.0

from sys.database_files

and please post back the results and we can diagnose what happens.

HTH,

JY

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Let us say that you did an update on a big table row by row. All these commands are written to the distribution DB. Just because the Commands have been executed and the commands are deleted from the distribution does not mean that it will reclaim the space used by the DB. (From the best of my knowledge)

-Roy

noeld SSC Guru Points: 96590 More actions · Answer 3

Distribution database is like any other DB. If you insert a lot of commands it has to grow.

File size management is up to you!

* Noel

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 4

Hello!

Thanks for your replies! I did figure out that because of my retention policy (72) in distribution database, old transactions haven't been deleted immediately. I see that space is being reclaimed now.

Thanks,

Igor

noeld SSC Guru Points: 96590 More actions · Answer 5

Good you figure it out.

Retention policy > 0 is usually not a bad thing when not a lot of transactions go through the distribution DB. It is not that good when the opposite happens.

* Noel