1

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?

Improve this question
1
  • have you tried running a full backup, If I remember correctly - this will clear out the log file. It may not be the right answer, but hopefully will give you more time until you do. other than that all I can suggest is playing with the log settings so it doesn't grow so quickly.
    – Mr Shoubs
    Oct 13, 2010 at 8:12

4 Answers 4

Reset to default
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.

Improve this answer
2
  • Hi, thanks for the suggestion. The answer is yes I have, with the larger databases but not in this case. It is marked down as "one of the things to try"!
    – SeanDav
    Oct 12, 2010 at 14:49
  • If the problem is with the backup/restore the wizard is performing this should definitely solve the issue since the wizard will only be making changes to the msdb database (on both primary and secondary) if the database is already restored with norecovery.
    – tcnolan
    Oct 13, 2010 at 7:29
0

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.

Improve this answer
0
0

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

Improve this answer
4
  • We back up the log file every day, a decision that was made before my time as I know it can cause fragmentation. Thanks for that link, I will check it out now.
    – SeanDav
    Oct 12, 2010 at 13:41
  • 1
    Actually, it's not the backups, it's constant filegrowth in small chunks that will cause VLF fragmentation. You mentioned shrinking the log file above. If the file has undergone a lot of shrinking/growing, it could be a big problem. Definitely review the link Mitch pointed out.
    – Pam Lahoud
    Oct 13, 2010 at 4:16
  • I am going to try the suggestions in that article - just have to wait 1 more day as the particular database is being used for some critical reports and don't want to risk anything until then. Thanks for the clarification bluefooted.
    – SeanDav
    Oct 13, 2010 at 7:07
  • I reduced VLF count from over 400 to 12 and followed the advice in the linked article. To no avail. As soon as I backup the database the log file starts going crazy.
    – SeanDav
    Oct 13, 2010 at 13:29
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.