April 22, 2009 at 9:03 pm
today i found that the distributor cleanup agent wasnt running. I then fixed it and it started to clean up records from the distribution.ldf, this was fine but now i am left with a massive distribution.mdf file 120gigs. what is the best way to shrink this down?
Can i upset the replication process by tampering with it?
Any thoughts appreciated.
😉
April 23, 2009 at 2:05 am
Please check size in terms of data present and available free space on the distribution database.
In SMS you can do it by right clicking on database --> Properties ,
If the free space is more than 25% then plan to shrink it, link below is related topic you can review.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111857
Cheer Satish 🙂
April 23, 2009 at 3:48 am
Sure i understand shrinking will make it slightly smaller and keeping all the data.
But the distribution database for replicated transaction is massive due to a inactive distributor clean up agent purging the transaction log.
i have since truncated the trans log from 66gig to 2meg. I am left with a database file still of 120gig. To my understanding i do not need the data within the distribution file but i am unsure exactly. can i just delete all the records.
I think that database should only be 100meg or so?
I need to recover that used disk space. perhaps a shrink may bring it down to 60gig if i am lucky.
but i want to purge most of the data. I am also unsure of the dangers of doing so?
Its just a database to hold transaction during the replication process, if i make sure no transaction are taking place would it be ok to delete all the records?
April 23, 2009 at 7:44 am
The distribution agent cleanup job removes rows from msrepl_transactions and msrepl_commands which have either been replicated or are beyond the retention period so it won't have cleared out the distribution database log file.
The only reason your distribution database log file would be large is if you have some huge transaction being written by a Log Reader Agent.
If the distribution agent cleanup job is running but not removing any rows from the msrepl_transactions and msrepl_commands tables in the distribution database then there is likely something wrong with the distribution agent so you should investigate that. They will be removed once they have been in the distribution database for longer than the retention period but then you will have lost transactions you intended to replicate.
You can remove rows from msrepl_transactions and msrepl_commands but it's an absolute last resort.
Mike
April 23, 2009 at 3:17 pm
Thanks Mike that answers my question.
Will proceed with caution.:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply