September 30, 2008 at 12:05 pm
Hopefully there's someone who's seen this or has some suggestions that we haven't tried.
The problem we have that restores for one of our log shipped databases are starting to slow down. The are still completing so this isn't an "error" situation but if the trend continues we're going to have some serious problems.
The setup:
SQL 2005 Enterprise. OLTP database is log shipped to a second SQL 2005 Enterprise server. Multiple databases are being log shipped but only one is starting to exhibit the problem. Inserts are increasing on the OLTP database but not to a degree that would explain a 3 fold increase in restore times.
Backups occur every fifteen minutes. Restores that were taking a minute or so are now taking 3 or 4 minutes. Multiplied by 4 logs an hour, what was taking only 3 or 4 minutes is now frequently taking 15 minutes or more. The increase in trans/sec is only about 15% over last month. That increase has occurred across all log shipped databases so I'm inclined to think that the increased traffic isn't a signifigant factor.
Some things that have been changed recently:
Insert triggers have been added to many tables to maintain integrity. Tables have been partitioned and a significant amount of data has been archived out of old partitions reducing the total size the database by more than 50%. A couple of files in non-partitioned filegroups have been shrunk to release needed space to the OS. Those files mostly contain nonclustered indexes of non-partitioned tables.
Any thoughts or suggestions would be appreciated.
THanks,
September 30, 2008 at 6:41 pm
Have you checked to see if the log file size is getting larger over the course of time?
Fraggle
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply