Problem Solved (Kind of...)
I am slightly embarrassed to admit that this behaviour was caused by a stored procedure called as part of an inhouse app. However, even having fixed the bug I still don't know why it was triggered by a backup!
This was the offending piece of code:
SELECT DISTINCT T.*, TL.RunDate FROM TaskLog TL INNER JOIN Task T ON TL.TaskID = T.ID WHERE TL.RerunFlag = 1 UPDATE TaskLog SET RerunFlag = 0
Changed last line to:
UPDATE TaskLog SET RerunFlag = 0 WHERE RerunFlag = 1
Update to Problem
I did a full backup of the database and that caused the log file to start growing uncontrollably. So it is not the log shipping process per se, but simply the first step - namely backing up the database that causes the issue. I checked the number of VLF's and it was over 400 yesterday, currently nearly 200 so that is definitely an issue.
Simple Statement of Problem:
Log file of a database normally around 80mb expands continually to 10’s of gigabytes with no sign of stopping if I try to initiate log shipping on the database on SQL Server 2005. DBCC SHRINKFILE has no effect.
I have been implementing log shipping successfully on several databases but having strange behaviour on 1 particular database on SQL Server 2005. Normally this database is around 80mb data with 80mb log file for a total size of around 160mb. It does not usually vary a lot in size during the course of the day. The problems start when I try to initiate log shipping on this database using the log shipping wizard.
The wizard gets to the first step which is backup the database and reaches 100% complete. It then stops/pauses here and never reaches the next step. No error message is generated and SSMS itself is responsive. It simply appears that the wizard is taking literally forever. I stop and kill the process, but the damage has already begun. From this moment on until the time the log file is backed up and shrunk the log file keeps on expanding at the rate of around 100mb every 5 minutes.
The users are not seemingly effected (thank goodness).
Any ideas of causes and for solutions?