How can we help you today? How can we help you today?
Dowdian
Activity overview Badges (0) Posts (1) Comments (6)

Comments

Sort by recent activity
Yes, there are a lot. The server hosts 26 databases. I do full's nightly and TLog's hourly on each. I keep the files for seven days on disk. I've updated the job to segregate the different database's backups into seperate folders and reset the ERASEFILES = 7 option. I believe we've got it [image] , but I'll post the final results tomorrow. Thanks again. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Long Running Backup Jobs
Yes, there are a lot. The server hosts 26 databases. I do full's nightly and TLog's hourly on each. I keep the files for seven days on disk. I've updated the job to segregate the different datab...
0 votes
The job ran last night in 45 minutes, right back where it aught to be. Thanks very much for your help with this issue. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Long Running Backup Jobs
The job ran last night in 45 minutes, right back where it aught to be. Thanks very much for your help with this issue.
0 votes
I agree with you about the pre or post processing tasks being the probable culprit. I ran the statement manually this morning having removed the ERASEFILES option and found that the job finished in 16 minutes (40% improvement over yesterday). I've modified the scheduled job and will post the results tomorrow. Thanks for your help on this. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Long Running Backup Jobs
I agree with you about the pre or post processing tasks being the probable culprit. I ran the statement manually this morning having removed the ERASEFILES option and found that the job finished i...
0 votes
Removing the ERASEFILES option brought the job run time right back down to normal, about 35 minutes. Any thoughts on where to go from here? / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Long Running Backup Jobs
Removing the ERASEFILES option brought the job run time right back down to normal, about 35 minutes. Any thoughts on where to go from here?
0 votes
I removed ERASEFILES_ATSTART = 6 and replaced it with ERASEFILES = 7 on 02/01 and have not seen any change in the job's run-time. I ran the command from Query Analyzer. It completed successfully in about 24 minutes. Two differences that might account for this is that there were no files old enough to delete (they were deleted early this morning), and backup jobs run much faster outside my normal backup window because there's no contention from all the other backup jobs. After looking over last nights log, it looks like the delay is still occurring just after the file deletion step, so I modified the statement to delete files that were six days old rather that seven. This modified statement ran in Query Analyzer in about 25 minutes. If it's contention for resources, why then the sudden change on the 23rd? / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Long Running Backup Jobs
I removed ERASEFILES_ATSTART = 6 and replaced it with ERASEFILES = 7 on 02/01 and have not seen any change in the job's run-time. I ran the command from Query Analyzer. It completed successfully i...
0 votes
Here's the command I'm using: DECLARE @exitcode int DECLARE @sqlerrorcode int exec master..sqlbackup N'-SQL "BACKUP DATABASES EXCLUDE [master, msdb, model] TO DISK = ''\\sqldata\data\SQLBackups\hprhsdata\backup\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', ERASEFILES = 7, COMPRESSION = 2, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT IF (@exitcode <>0) OR (@sqlerrorcode <> 0) BEGIN RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode) END It looks like the log files are too large to include in the post. I've included sections of two log files below. The first is from 01/23 and is typical of the performance I had seen up till then. The second is from 01/24 and shows the performance I've seen since then. If you 'd like to see the logs in their entirety, let me know and I can email them to you. ... 1/23/2007 2:53:17 AM: Backing up TelAll (full database) to: \\hprhsdata\Data\SQLBackups\HPRHSDATA\Backup\FULL_(local)_TelAll_20070123_025317.sqb 1/23/2007 2:53:17 AM: BACKUP DATABASE [TelAll] TO DISK = '\\hprhsdata\Data\SQLBackups\HPRHSDATA\Backup\FULL_(local)_TelAll_20070123_025317.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES_ATSTART = 6, COMPRESSION = 2, THREADS = 1 1/23/2007 2:53:25 AM: Deleting old backup file: \\hprhsdata\Data\SQLBackups\HPRHSDATA\Backup\FULL_(local)_TelAll_20070117_024549.sqb 1/23/2007 2:55:32 AM: Backup data size : 77.938 MB 1/23/2007 2:55:32 AM: Compressed data size: 36.464 MB 1/23/2007 2:55:32 AM: Compression rate : 53.21% Processed 9880 pages for database 'TelAll', file 'TelAll_dat' on file 1. Processed 1 pages for database 'TelAll', file 'TelAll_log' on file 1. BACKUP DATABASE successfully processed 9881 pages in 3.397 seconds (23.826 MB/sec). -------------------------------------------------------------------------------- ... ... 1/24/2007 3:13:24 AM: Backing up TelAll (full database) to: \\hprhsdata\Data\SQLBackups\HPRHSDATA\Backup\FULL_(local)_TelAll_20070124_031318.sqb 1/24/2007 3:13:24 AM: BACKUP DATABASE [TelAll] TO DISK = '\\hprhsdata\Data\SQLBackups\HPRHSDATA\Backup\FULL_(local)_TelAll_20070124_031318.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES_ATSTART = 6, COMPRESSION = 2, THREADS = 1 1/24/2007 3:44:07 AM: Backup data size : 75.625 MB 1/24/2007 3:44:07 AM: Compressed data size: 34.652 MB 1/24/2007 3:44:07 AM: Compression rate : 54.18% Processed 9584 pages for database 'TelAll', file 'TelAll_dat' on file 1. Processed 1 pages for database 'TelAll', file 'TelAll_log' on file 1. BACKUP DATABASE successfully processed 9585 pages in 3.314 seconds (23.691 MB/sec). -------------------------------------------------------------------------------- ... / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Long Running Backup Jobs
Here's the command I'm using:DECLARE @exitcode int DECLARE @sqlerrorcode int exec master..sqlbackup N'-SQL "BACKUP DATABASES EXCLUDE [master, msdb, model] TO DISK = ''\\sqldata\data\SQLBac...
0 votes
Total activity
8
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1