Home / server / Questions / 190098
In Process
SeanDav
Asked: 2010-10-13 05:11:22 +0800 CST

SQL Database Log file just keeps on growing

  • 772

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.

Detailed Statement:

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?

windows sql-server log-files

4 Answers

  1. Have you tried taking your source backup and restoring to your target server using the NORECOVERY STANDBY options and then doing the log shipping install? I have used this to bypass the need for the "wizard" to take a full backup and restore it to the target server.

    • 1

  2. Do you regularly backup the transaction log file? (You should)

    It sounds like you have Virtual Log File (VLF) fragmentation. I would suggest reading: 8 Steps to better Transaction Log throughput

    • 0

  3. It definetely sounds like there is something going on with the Transaction Log.

    Have you looked at the Log Shipping status Report? This might give you and idea of what is happening to this database when it hangs up. Specifically you'll want to figure out what happens when it tries to copy the log.

    • 0

  4. Given your symptoms I guess the log file growth is actually correct. Is the database normally simple recovery model? Run profiler for a short time againts this database and you'll probably find lots of updates (or deletes or inserts) happening.

    My thinking is this,
    Normally SQL server is re-using the log space. Once you start the process of log shipping though SQL server can no longer free the log until it has been shipped (or at least backed up ready to be shipped). The log shipping processes starts as soon as you take the full backup to use to initialise the partner database which is why your symptoms appear as soon as the backup happens. The backup isn't causing the problem the problem is with whatever updates are being run so frequently.

    • 0