Need some help here. Today I had our DISTRIBUTION Database Log File Grow from 100mb - 85GB. This crashed the server by taking up all the disk space. After a quick review i noticed the following job running and seemed to not stop. This usually ran every 10 mins but this time if never stopped.
Agent history clean up: distribution
which executes the following T-SQL script.
EXEC dbo.sp_MShistory_cleanup @history_retention = 48
To remedy this and get the server back online, i did the flowing.
STOP/START SQL Services
-- Changed the database recovery model to SIMPLE.
ALTER DATABASE DISTRIBUTION
SET RECOVERY SIMPLE;
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DISTRIBUTION_Log, 1);
This worked fine and i recovered the 85GB back.
However, As soon as I re-ran that job, the LOG grew again and eventually took all the space. I redid the same steps above but this time Disabled that job. The server is running optimally again and replication is working fine.
The distribution_Log file options are
In Percent 10%
MAximum File Growth (MB) 2,097,152 or 2TB.
These are the defaults. The TAble distribution.sp_MShistory_cleanup has 41174557 records and that job should technically cleanup this table for 48hr back. The strange part is that the table still has the first initializing record plus the latest ones.
Initializing 0 0x0000000011E41BF5 0 2010-12-05 23:18:03.390
Waiting 60 seconds 0 0x00000000173A7AA1 1 2011-03-30 20:22:26.843
Any idea what i should do? I don't want to blow up this DB and recreate all the replications snapshots and rules.
SQL 2005 Standard 9.0.4035