December 30, 2010 at 6:40 am
We currently have a sql 2005 database (compability mode 80)
The recovery model is simple
We do transaction log backups every 15 minutes
The .mdf file is 123,418,624 KB on the disk (18% free space)
The .ldf file is 568,896 KB on the disk (96% free space)
For some reason the backup is taking almost 7 hours and the backup file is 103,991,857 KB
These seems like an unsually long time for the backup to take --- and the .bak file seems large to me.
The T-SQL script for the backup job is below ---- can anyone point out what they think might be the cause the backup task taking so long?
BACKUP DATABASE [LBMXSolutionCentre] TO DISK = N'E:\Databases\Backup\LBMXSolutionCentre_backup_201012300832.bak' WITH NOFORMAT, NOINIT, NAME = N'LBMXSolutionCentre_backup_20101230083246', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'LBMXSolutionCentre' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'LBMXSolutionCentre' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''LBMXSolutionCentre'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'E:\Databases\Backup\LBMXSolutionCentre_backup_201012300832.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
December 30, 2010 at 8:31 am
7 hours to backup 99 GB seems to be abnormal. Did you check if any other process is blocking this Backup session? Also check the System Event Log for any disk related errors.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 30, 2010 at 8:42 am
Why are you doing log backups on a database that's in Simple recovery mode?
John
December 30, 2010 at 8:58 am
Checked the event log -- nothing (no errors)
There was effectively nothing happening with the db during this period -- a small amount of data was added
December 30, 2010 at 9:00 am
You are right -- it needs to be changed to full.
The transactions backups were created with the intention of truncating the log files....
I did not set this up -- I am just untangling all the wires.
Do you think that setting it to "full" will rectify my timing issues?
December 30, 2010 at 9:02 am
To be honest, it's unlikely. But, looking at it from the other direction, if you intended to keep it Simple then you'd have been well advised to stop the log backups, since they would be using resources unnecessarily.
John
December 30, 2010 at 10:43 am
Try some of the timing suggestions in this link: Speeding up backups. Should tell you if the problem lies with reading or writing the backup data (or both).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply