Log file was growing unexpectedly in Simple Recovery Mode during huge data purging process.
It was strange. My SQL Server is 2000 SP4.
Solution:
1. Check Trans Log file
The log was keeping growing.
2. Check Active Transaction
USE [yourDB]
GO
DBCC OPENTRAN
GO
3. Manually reset DB to Simple Recovery Mode
SP_DBOPTION [yourDB]
SP_DBOPTION [yourDB], 'trunc. log on chkpt.', 'true'
SP_DBOPTION [yourDB]
4. Shrink Trans log to the target size.
USE [yourDB]
GO
DBCC SHRINKFILE ('yourDBLog' , TargetSize)
GO
5. Monitor Trans Log growth.
Inactive transaction log entries should be cleared out at every checkpoint.
Should leave the log to a certain size, does NOT make sense to shrink it again unless an unusual over-volume transactions happen.
