Slow Tran Log backup

  • Hi guys,

    Been having some problems with the tran log backup on one of our production servers.

    Rebooted the production server last night, but the tran log backup has gone from taking a few seconds before today to > 10 minutes now.

    I've managed to shrink the tran log from > 42GB (!) to ~100MB using DBCC SHRINKFILE, but the backups are still taking 10 minutes upwards, and as the log is backed up every 15 minutes, this is causing continual performance issues.

    DBCC LOGINFO returns 230 rows, many have status 2.

    There don't appear to be any long-running open transactions (whenever I run OPENTRAN it returns a tran only a few seconds old).

    Database is replicated (it's the publisher), but replication is bang up to date.

    We've eliminated disk /IO/Anti-Virus/Network problems, so we're fairly sure it's a DB issue.

    Any ideas?

    Cheers 🙂

  • Are you backing up to a device or individual t-log files?

    If you're backing up to a device, the lookups on the device that initially occur in the backup process can be quite slow if you have a large number of previous backups on the device.

    Your friendly High-Tech Janitor... 🙂

  • It's backed up to disk file, and old backups are removed after 1 day, so there's not too many old files.

    Server was rebooted again last night, backups still taking minutes to complete.

  • Are you having issues with your disk drives where the backups are being written to? You may want to check perfmon or profiler for I/O metrics (or disk manager) or even the eventlog to be sure it's not hardware related. Is the disk file local or a remote server ? Are you testing the restores of these as well, to ensure the reliability of your backups?

    -- You can't be late until you show up.

  • Hi Terry, no I/O problems as far as we can see.

    The disks are on a SAN connected by fibre link to the SQL Server, so pretty quick in all!

    Restores have not been tested. According to the backup logs, data verification only takes a second or two, all the time seems to be taken up creating the file.

    Only other thing we can think of is that SQL Server is hogging all the server mem & not leaving enough for the OS. (Box only has 10-50MB free RAM according to perfmon).

    I've changed the max server mem size to leave 4GB free on the server for the OS, but perfmon is still only reporting 10-50MB free RAM.

    Very annoying!

  • I've used the same setup for years and never had issues. Recommended, leave 1-1.5 GB for OS and let SQL have the rest, if this is a dedicated SQL server. No issues with the NIC or network traffic?

    -- You can't be late until you show up.

  • Sorted it in the end, after sql server was restarted with the new max memory setting, OS has around 2GB free RAM & the backup times are back to normal.

    Changing the max mem size is supposed to be an on-line operation, but it seems sql server couldn't release the memory until it was restarted!

    Cheers for all your responses 🙂

  • Glad you sorted it out. It is an online operation, I forgot that SQL will grab as much as it can (up to the max, if there is one) but not release it.

    -- You can't be late until you show up.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply