August 3, 2007 at 4:44 am
Hi
We have a backup job that is running on a stats database that is taking over 2 days to complete. The problem is is that the transaction log eventually gets to capacity and we have to kill the backup. The recovery mode at the moment is simple. I have heard that if I put it into full I will be able to truncate the log during the backup and so not have to worry about the log getting full. This is supposedly possible in 2005 but wasnt in 2000.
So thats one thing I can try.
The other is doing filegroup backups instead full backups
Has anyone had to deal with this kind of problem in 2005 and if so how did you resolve it
thanks
August 3, 2007 at 6:30 am
Yes, very definitely, change the recovery model to Full. This not only helps you in regard to automatically truncating the log file, it also allows you to perform transaction log backups throughout the day. Once the log is truncated, you may need to manually shrink it in order to recovery space.
Also, if you have problems with long running backups, consider a third-party backup program that can compress the backups, saving a great amount of backup time.
Brad
Microsoft SQL Server MVP
Brad M. McGehee
DBA
August 3, 2007 at 8:24 am
Hi
I just tried to run Backup Log DBNAME with truncate_only and DBCC shrinkfile on a test database in FULL recovery mode while a full backup is running but it wouldnt let me. So am I right to think that having th DB in FULL recovery mode wont help me to overcome the problem because the transaction log is just going to increase and I cant truncate it and shrink it during the backup.
Are filegroup backups the only solution for this?
August 3, 2007 at 8:37 am
You can't run two backups at the same time. This is what is causing your current issue.
Brad
Brad M. McGehee
DBA
August 3, 2007 at 9:01 am
You can backup the log to a file while a full backup is running but you cant run a truncate or shrink.
Backing up the log to a file doesnt shrink the physical size of the ldf file so this new feature doesnt help me.
So it looks like filegroup backups. The problem is I have to get a full backup done first. Bad place to be. I hope I dont have to resort to prayer.
thanks for responding
August 3, 2007 at 9:10 am
How big is your database?
August 3, 2007 at 9:14 am
Over 2 terabytes
August 3, 2007 at 9:17 am
Are you backing up to a local disk or across your network to a network share?
August 3, 2007 at 9:38 am
To a network share using redgate sql backup
August 3, 2007 at 10:09 am
That may be part of the problem. Do you have the space available on your server to backup directly yo disk and then move the backup file?
August 8, 2007 at 3:26 pm
You problem sounds like a hardware bottleneck, like not enough bandwidth on your network connection, poor performing disk drives on your target or source server, or not enough memory or CPU capacity.
If that is the case, you will likely need a hardware upgrade.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply