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

Comments

Sort by recent activity
results sent. / comments
  1. Community
  2. SQL Backup
  3. Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?
results sent.
0 votes
Run 1 as the standard command I've been using returns:  BACKUP DATABASE successfully processed 4008260 pages in 377.103 seconds (83.039 MB/sec). Run 2 with fileoptions = 16 returns: BACKUP DATABASE successfully processed 4008260 pages in 374.802 seconds (83.549 MB/sec). Run 3 with fileoptions = 8 is actually worse with: BACKUP DATABASE successfully processed 4008260 pages in 425.041 seconds (73.674 MB/sec) / comments
  1. Community
  2. SQL Backup
  3. Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?
Run 1 as the standard command I've been using returns: BACKUP DATABASE successfully processed 4008260 pages in 377.103 seconds (83.039 MB/sec).Run 2 with fileoptions = 16 returns:BACKUP DATABASE su...
0 votes
I don't have a db that small on that box but I did run the test on the smallest I have and just like the above 1 runs at 176mb a second while the other is at 88mb a second. The 2 instances reside on the same host and the 2 drives are on the same shared storage array so there should be no difference between the backup speeds. If anything the 2nd cmd should be quicker as there's less activity on the 2nd instance. There's no humans on either instance and the 1st is the backside of an AAG mirror so is receiving prod updates and does backups. The other, slower backup is a snapshot of the same instance config with a different name so in my mind they should be equal or close to it. / comments
  1. Community
  2. SQL Backup
  3. Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?
I don't have a db that small on that box but I did run the test on the smallest I have and just like the above 1 runs at 176mb a second while the other is at 88mb a second. The 2 instances reside o...
0 votes
That's what i thought so had the network guy do that test and he's reporting 240+mb per second on either. / comments
  1. Community
  2. SQL Backup
  3. Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?
That's what i thought so had the network guy do that test and he's reporting 240+mb per second on either.
0 votes
There is no encryption or compression set for either run so in this case a nonfactor. I've used the same command with the only difference being the path in the job and in the adhoc sql and the adhoc takes twice the time. / comments
  1. Community
  2. SQL Backup
  3. Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?
There is no encryption or compression set for either run so in this case a nonfactor. I've used the same command with the only difference being the path in the job and in the adhoc sql and the adho...
0 votes
here are the 2 log entries, the 1st is the fast run and I see there's 20 extra gb of data in the 2nd run but that shouldn't add 90 minutes. SQL Backup log file 9.1.6.257   -SQL "BACKUP DATABASE [FranStore2004] TO DISK = '\\CG1SQLP02\Backup1$\CISBackup\<database>\<AUTO>.sqb' WITH CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 0, THREADCOUNT = 7, CHECK_PREFERRED_AG_REPLICA, SECONDARY_REPLICA_COPY_ONLY "   -----------------------  PROCESSES COMPLETED SUCCESSFULLY   --------------------   2020-02-20 3:09:00 PM: Backing up FranStore2004 (full database) on CIS2017P00 instance to: 2020-02-20 3:09:00 PM:   \\CG1SQLP02\Backup1$\CISBackup\FranStore2004\FULL_CIS2017P00_FranStore2004_20200220_150900.sqb   2020-02-20 3:09:00 PM: BACKUP DATABASE [FranStore2004]  TO VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC280', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28001', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC 28002', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28003', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28004', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7-450E-8DB2-CFA141EFC28005', VIRTUAL_DEVICE = 'SQLBACKUP_9128A413-ADA7 -450E-8DB2-CFA141EFC28006' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (FranStore2004), 2020-02-20 3:09:00 PM', DESCRIPTION = N'Backup on 2020-02-20 3:09:00 PM  Server: CG1SQLP02\CIS2017P00  Database:  FranStore2004', FORMAT, CHECKSUM, COPY_ONLY   2020-02-20 3:58:31 PM: Backup data size    : 478.607 GB   2020-02-20 3:58:31 PM: Processed 53858632 pages for database 'FranStore2004', file 'Franstore2004_data' on file 1. 2020-02-20 3:58:31 PM: Processed 8865385 pages for database 'FranStore2004', file 'Franstore2004_log' on file 1. 2020-02-20 3:58:31 PM: BACKUP DATABASE successfully processed 62724017 pages in 2970.163 seconds (164.984 MB/sec). 2020-02-20 3:58:31 PM: SQL Backup process ended. -----------------------------------------------------------------------------------------------------   SQL Backup log file 9.1.6.257   -SQL "BACKUP DATABASE [FranStore2004] TO DISK = '\\cg1redgtp01\testbackup\CIS\<database>\<AUTO>.sqb' WITH ERASEFILES_ATSTART = 1, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 0, THREADCOUNT = 7, CHECK_PREFERRED_AG_REPLICA,  COPY_ONLY "   -----------------------  PROCESSES COMPLETED SUCCESSFULLY   --------------------   2020-02-20 4:05:07 PM: Backing up FranStore2004 (full database) on CIS2017P00 instance to: 2020-02-20 4:05:07 PM:   \\cg1redgtp01\testbackup\CIS\FranStore2004\FULL_CIS2017P00_FranStore2004_20200220_160507.sqb   2020-02-20 4:05:07 PM: BACKUP DATABASE [FranStore2004]  TO VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD01', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE69 1FD02', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD03', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD04', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4-48AD-8AC6-70F5FCE691FD05', VIRTUAL_DEVICE = 'SQLBACKUP_AEDFC74B-29D4 -48AD-8AC6-70F5FCE691FD06' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (FranStore2004), 2020-02-20 4:05:07 PM', DESCRIPTION = N'Backup on 2020-02-20 4:05:07 PM  Server: CG1SQLP02\CIS2017P00  Database:  FranStore2004', FORMAT, CHECKSUM, COPY_ONLY   2020-02-20 6:39:18 PM: Backup data size    : 499.799 GB   2020-02-20 6:39:18 PM: Processed 53870048 pages for database 'FranStore2004', file 'Franstore2004_data' on file 1. 2020-02-20 6:39:18 PM: Processed 11631321 pages for database 'FranStore2004', file 'Franstore2004_log' on file 1. 2020-02-20 6:39:18 PM: BACKUP DATABASE successfully processed 65501369 pages in 9247.554 seconds (55.336 MB/sec). 2020-02-20 6:39:18 PM: SQL Backup process ended. / comments
  1. Community
  2. SQL Backup
  3. Why would a scheduled backup run twice as fast as a non-scheduled backup on same Db?
here are the 2 log entries, the 1st is the fast run and I see there's 20 extra gb of data in the 2nd run but that shouldn't add 90 minutes.SQL Backup log file 9.1.6.257 -SQL "BACKUP DATABASE [FranS...
0 votes
Perfect, worked as advertised, thanks / comments
  1. Community
  2. SQL Backup Previous Versions
  3. What causes the "error opening mutex" error in Backup Pro 9 when accessing my AAG replica?
Perfect, worked as advertised, thanks
0 votes
Total activity
10
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
2