Distribution_Log File growth out of control. - Agent history clean up: distribution job not working.
Hi Experts,
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;
GO
-- 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
Enable autogrowth
File Growth
In Percent 10%
MAximum File Growth (MB) 2,097,152 or 2TB.
These are the defaults. The TAble distribution.sp_MShistory_
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.
PLATFORM:
SQL 2005 Standard 9.0.4035
-Hades666
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;
GO
-- 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
Enable autogrowth
File Growth
In Percent 10%
MAximum File Growth (MB) 2,097,152 or 2TB.
These are the defaults. The TAble distribution.sp_MShistory_
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.
PLATFORM:
SQL 2005 Standard 9.0.4035
-Hades666
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQLSergentMike
If that database is growing then replication isn't replicating the transactions to the subscriber or as said above your retention period is too long. The Distribution database shouldn't be that large under normal circumstances. The only other issue that I can think of that would cause that type of growth would be deferred transaction.
Somehow I suspect the author is MIA or no longer cares...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A little more detailed then the original post. He gets the points but my comment was the solution :)