How can we help you today? How can we help you today?
kmadsen
Activity overview Badges (0) Posts (3) Comments (17)

Activity overview

Latest activity by kmadsen

Intermittant Transaction Log Backup Failures
  1. Community
  2. SQL Backup Previous Versions
I get intermittant backup failures when trying to backup the transaction logs. the following is the error a typical error in the SQL Errorlog. BackupVirtualDeviceSet::Initialize: Open failure on ba...
4 followers 6 comments 0 votes
SQL Server Hardware Upgrade
  1. Community
  2. SQL Backup Previous Versions
I wil lbe upgrading the hardware on one of my production SQL Servers soon that has SQL BAckup installed and need to know what steps I need to take to move the license/activation from the old hardwa...
1 follower 1 comment 0 votes
I deleted 12K+ of the log files and now it runs much faster. Is there ever going to be an option to delete older log files like the erase files option? / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
I deleted 12K+ of the log files and now it runs much faster. Is there ever going to be an option to delete older log files like the erase files option?
0 votes
Command ran from QA = exec spLogDBBackup '\\hbhscorp4\sqlbak\sqlcmsbk\',3,'CatalogTraining','distribution' Definition of spLogDBBackup CREATE procedure spLogDBBackup @BackupPath sysname, @RetentionDays tinyint, @Skip1 sysname = NULL, @Skip2 sysname = NULL, @Skip3 sysname = NULL, @Skip4 sysname = NULL, @Skip5 sysname = NULL, @Skip6 sysname = NULL, @Skip7 sysname = NULL, @Skip8 sysname = NULL, @Skip9 sysname = NULL, @Skip10 sysname = NULL as --Intialize the database to be skipped list create table #SkipDBs (DBName sysname null) insert into #SkipDBs select @Skip1 insert into #SkipDBs select @Skip2 insert into #SkipDBs select @Skip3 insert into #SkipDBs select @Skip4 insert into #SkipDBs select @Skip5 insert into #SkipDBs select @Skip6 insert into #SkipDBs select @Skip7 insert into #SkipDBs select @Skip8 insert into #SkipDBs select @Skip9 insert into #SkipDBs select @Skip10 --Create the cursor to loop through the databases respecting the skip list declare @DBName sysname declare DB_Cursor Cursor for select name from master.dbo.sysdatabases where name not in (select DBName from #SkipDBs where DBName is not null) and name not in ('TempDB','Master','Model','msdb') open DB_Cursor fetch next from DB_Cursor into @DBName @fetch_status = 0 begin -- print @DBName -- select getdate() exec spDBBackupLog @DBName, @BackupPath, @RetentionDays -- select getdate() fetch next from DB_Cursor into @DBName end close DB_Cursor deallocate DB_Cursor GO Definition of spDBBackupLog CREATE procedure spDBBackupLog @DB_Name sysname, @BackupPath sysname, @RetentionDays tinyint as declare @BKFileName nvarchar(284) declare @LogFileName nvarchar(284) declare @CMDString nvarchar(255) declare @BackupString nvarchar(4000) declare @Cur_Date datetime select @Cur_date = getdate() --Verify the Database Name is valid if not exists (select name from master.dbo.sysdatabases where name=@DB_Name) begin print 'The database name '+@DB_Name+' is not found in Master.dbo.Sysdatabases' print 'Usage: exec spDBBackupFull ''DatabaseName'', ''PathToBackup'', NumberOfBackupFilesToRetain' return 1 end --Verify the Backup Path is valid select @CMDString='dir '+@BackupPath create table #DirOutput (Line nvarchar(255)) insert into #DirOutput exec master.dbo.xp_cmdshell @CMDString if exists (select line from #DirOutput where line='File Not Found') begin print 'The Backup Path '+@BackupPath+' Does not exist' print 'Usage: exec spDBBackupFull ''DatabaseName'', ''PathToBackup'', NumberOfBackupFilesToRetain' return 1 end --Normalize the backup path with an ending if (select right(@BackupPath,1))<>'\' select @BackupPath=@BackupPath+'\' --Build the Backup Filename including the path select @BKFileName=@BackupPath+@DB_Name+'_TLog_' +(select right('0000'+cast(datepart(yyyy,@Cur_Date) as nvarchar(4)),4)) +(select right('00'+cast(datepart(mm,@Cur_Date) as nvarchar(2)),2)) +(select right('00'+cast(datepart(dd,@Cur_Date) as nvarchar(2))+'_',3)) +(select right('00'+cast(datepart(hh,@Cur_Date) as nvarchar(2)),2)) +(select right('00'+cast(datepart(mi,@Cur_Date) as nvarchar(2)),2)) +(select right('00'+cast(datepart(ss,@Cur_Date) as nvarchar(2)),2)) +'.TRN' --Build the Log Filename including the path select @LogFileName=@BackupPath+@DB_Name+'_TLog_' +(select right('0000'+cast(datepart(yyyy,@Cur_Date) as nvarchar(4)),4)) +(select right('00'+cast(datepart(mm,@Cur_Date) as nvarchar(2)),2)) +(select right('00'+cast(datepart(dd,@Cur_Date) as nvarchar(2))+'_',3)) +(select right('00'+cast(datepart(hh,@Cur_Date) as nvarchar(2)),2)) +(select right('00'+cast(datepart(mi,@Cur_Date) as nvarchar(2)),2)) +(select right('00'+cast(datepart(ss,@Cur_Date) as nvarchar(2)),2)) +'.Log' --Build the Backup Command String select @BackupString= 'exec master.dbo.sqlbackup ''-SQL "BACKUP LOG '+@DB_Name+ ' TO DISK = ['+@BKFileName+ -- '] WITH LOGTO = , ERASEFILES = '+cast(@RetentionDays as nvarchar(4))+'"''' '] with ERASEFILES = '+cast(@RetentionDays as nvarchar(4))+'"''' -- print @BackupString exec sp_executesql @BackupString GO QA Output (Note: The two select getdate() lines were uncommented for this run) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) 2005-06-16 11:35:03.883 (1 row(s) affected) (12846 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG Admin TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\Admin_TLog_20050616_113503.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up Admin (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\Admin_TLog_20050616_113503.TRN ... Backup data size : 832.000 KB Compressed data size: 15.500 KB Compression rate : 98.14% Process completed successfully. Processed 3 pages for database 'Admin', file 'Admin_Log' on file 1. BACKUP LOG successfully processed 3 pages in 0.058 seconds (0.344 MB/sec). (1 row affected) (10 row(s) affected) 2005-06-16 11:38:35.630 (1 row(s) affected) 2005-06-16 11:38:35.630 (1 row(s) affected) (12848 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG CMS2000 TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050616_113835.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up CMS2000 (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050616_113835.TRN ... Backup data size : 4.250 MB Compressed data size: 709.000 KB Compression rate : 83.71% Process completed successfully. Processed 420 pages for database 'CMS2000', file 'CMS2000_Log' on file 1. BACKUP LOG successfully processed 420 pages in 0.198 seconds (17.348 MB/sec). (1 row affected) (10 row(s) affected) 2005-06-16 11:41:37.317 (1 row(s) affected) 2005-06-16 11:41:37.317 (1 row(s) affected) (12850 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG CMSHeavenly TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\CMSHeavenly_TLog_20050616_114137.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up CMSHeavenly (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\CMSHeavenly_TLog_20050616_114137.TRN ... Backup data size : 704.000 KB Compressed data size: 23.500 KB Compression rate : 96.66% Process completed successfully. Processed 9 pages for database 'CMSHeavenly', file 'CMS2000_Log' on file 1. BACKUP LOG successfully processed 9 pages in 0.089 seconds (0.765 MB/sec). (1 row affected) (10 row(s) affected) 2005-06-16 11:44:34.393 (1 row(s) affected) 2005-06-16 11:44:34.393 (1 row(s) affected) (12852 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG eConnect TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\eConnect_TLog_20050616_114434.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up eConnect (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\eConnect_TLog_20050616_114434.TRN ... Backup data size : 896.000 KB Compressed data size: 66.500 KB Compression rate : 92.58% Process completed successfully. Processed 28 pages for database 'eConnect', file 'eConnect_Log' on file 1. BACKUP LOG successfully processed 28 pages in 0.089 seconds (2.531 MB/sec). (1 row affected) (10 row(s) affected) 2005-06-16 11:47:26.050 (1 row(s) affected) 2005-06-16 11:47:26.050 (1 row(s) affected) (12853 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG eConnectOregon TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\eConnectOregon_TLog_20050616_114726.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up eConnectOregon (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\eConnectOregon_TLog_20050616_114726.TRN ... Backup data size : 576.000 KB Compressed data size: 4.500 KB Compression rate : 99.22% Process completed successfully. BACKUP LOG successfully processed 0 pages in 0.005 seconds (0.000 MB/sec). (1 row affected) (9 row(s) affected) 2005-06-16 11:49:52.453 (1 row(s) affected) 2005-06-16 11:49:52.453 (1 row(s) affected) (12854 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG HOS TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\HOS_TLog_20050616_114952.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up HOS (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\HOS_TLog_20050616_114952.TRN ... Backup data size : 512.000 KB Compressed data size: 2.000 KB Compression rate : 99.61% Process completed successfully. BACKUP LOG successfully processed 0 pages in 0.046 seconds (0.000 MB/sec). (1 row affected) (9 row(s) affected) 2005-06-16 11:52:20.220 (1 row(s) affected) 2005-06-16 11:52:20.220 (1 row(s) affected) (12855 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG Northwind TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\Northwind_TLog_20050616_115220.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up Northwind (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\Northwind_TLog_20050616_115220.TRN ... Backup data size : 576.000 KB Compressed data size: 4.000 KB Compression rate : 99.31% Process completed successfully. BACKUP LOG successfully processed 0 pages in 0.009 seconds (0.000 MB/sec). (1 row affected) (9 row(s) affected) 2005-06-16 11:55:04.437 (1 row(s) affected) 2005-06-16 11:55:04.437 (1 row(s) affected) (12856 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG POrderApp TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\POrderApp_TLog_20050616_115504.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up POrderApp (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\POrderApp_TLog_20050616_115504.TRN ... Backup data size : 704.000 KB Compressed data size: 39.500 KB Compression rate : 94.39% Process completed successfully. Processed 15 pages for database 'POrderApp', file 'POrderApp_Log' on file 1. BACKUP LOG successfully processed 15 pages in 0.086 seconds (1.422 MB/sec). (1 row affected) (10 row(s) affected) 2005-06-16 11:58:07.593 (1 row(s) affected) 2005-06-16 11:58:07.593 (1 row(s) affected) (12857 row(s) affected) exec master.dbo.sqlbackup '-SQL "BACKUP LOG pubs TO DISK = [\\hbhscorp4\sqlbak\sqlcmsbk\pubs_TLog_20050616_115807.TRN] with ERASEFILES = 3"' Output from SQL Backup (3.2.0) SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Serial number: 010-005-019079-86FA Backing up pubs (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\pubs_TLog_20050616_115807.TRN ... Backup data size : 512.000 KB Compressed data size: 3.000 KB Compression rate : 99.41% Process completed successfully. BACKUP LOG successfully processed 0 pages in 0.014 seconds (0.000 MB/sec). (1 row affected) (9 row(s) affected) 2005-06-16 12:01:12.310 (1 row(s) affected) / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
Command ran from QA = exec spLogDBBackup '\\hbhscorp4\sqlbak\sqlcmsbk\',3,'CatalogTraining','distribution' Definition of spLogDBBackup CREATE procedure spLogDBBackup@BackupPath sysname,@RetentionDa...
0 votes
No significant change in backup time without the logto option. One thing I have seen while manualy running them is I get the following rowcounts back after the backup log successfull message... 1 row effected 9 rows affected 12829 rows affected (This number keeps increasing) / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
No significant change in backup time without the logto option. One thing I have seen while manualy running them is I get the following rowcounts back after the backup log successfull message... 1 r...
0 votes
Yes these are the timestamps as recorded on the remote machines. I did sync times on the two machines just before executing the backups to ensure that we were working off a common clock. I will be running without the logto option shortly. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
Yes these are the timestamps as recorded on the remote machines. I did sync times on the two machines just before executing the backups to ensure that we were working off a common clock. I will be ...
0 votes
First Profiler trace containing the backup commands... Event Class Application Name Text Data SPID Start Time Audit Logout SQLAgent - Job Manager 68 6/15/05 3:20 PM Audit Login OSQL-32 #NAME? SQL:BatchStarting OSQL-32 set quoted_identifier off 68 6/15/05 3:20 PM SQL:BatchStarting OSQL-32 BACKUP LOG [Admin] TO VIRTUAL_DEVICE = 'SQLBACKUP_49282749' WITH BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = '', DESCRIPTION = '', FORMAT Audit Logout OSQL-32 68 6/15/05 3:20 PM Audit Login OSQL-32 #NAME? SQL:BatchStarting OSQL-32 set quoted_identifier off 83 6/15/05 3:20 PM SQL:BatchStarting OSQL-32 SELECT TOP 1 a.first_lsn, a.last_lsn, a.checkpoint_lsn, a.database_backup_lsn FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id WHERE b.physical_device_name = 'SQLBACKUP_49282749' ORDER BY a.media_set_id DESC Audit Logout OSQL-32 83 6/15/05 3:20 PM Audit Login OSQL-32 #NAME? SQL:BatchStarting OSQL-32 set quoted_identifier off 79 6/15/05 3:20 PM SQL:BatchStarting OSQL-32 UPDATE msdb..backupmediafamily SET physical_device_name = '\\hbhscorp4\sqlbak\sqlcmsbk\Admin_TLog_20050615_152007.TRN' WHERE physical_device_name = 'SQLBACKUP_49282749' AND device_type = 7 SQL:BatchStarting OSQL-32 set quoted_identifier off 82 6/15/05 3:25 PM SQL:BatchStarting OSQL-32 BACKUP LOG [CMS2000] TO VIRTUAL_DEVICE = 'SQLBACKUP_166256317' WITH BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = '', DESCRIPTION = '', FORMAT Audit Logout OSQL-32 82 6/15/05 3:25 PM Audit Login OSQL-32 #NAME? SQL:BatchStarting OSQL-32 set quoted_identifier off 60 6/15/05 3:26 PM SQL:BatchStarting OSQL-32 SELECT TOP 1 a.first_lsn, a.last_lsn, a.checkpoint_lsn, a.database_backup_lsn FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id WHERE b.physical_device_name = 'SQLBACKUP_166256317' ORDER BY a.media_set_id DESC Audit Logout OSQL-32 60 6/15/05 3:26 PM Audit Login OSQL-32 #NAME? SQL:BatchStarting OSQL-32 set quoted_identifier off 78 6/15/05 3:26 PM SQL:BatchStarting OSQL-32 UPDATE msdb..backupmediafamily SET physical_device_name = '\\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050615_152433.TRN' WHERE physical_device_name = 'SQLBACKUP_166256317' AND device_type = 7 Audit Logout OSQL-32 78 6/15/05 3:26 PM Second the file date/time of the backup files and the logs Admin DB - TRN File = 3:20pm Log file = 3:24pm CMS2000 db - TRN File = 3:26pm log file = 3:28pm Third Log files Admin.log 6/15/2005 3:20:19 PM: SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 6/15/2005 3:20:19 PM: 6/15/2005 3:20:19 PM: Backing up Admin (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\Admin_TLog_20050615_152007.TRN ... 6/15/2005 3:20:19 PM: 6/15/2005 3:20:19 PM: BACKUP LOG [Admin] TO DISK = '\\hbhscorp4\sqlbak\sqlcmsbk\Admin_TLog_20050615_152007.TRN' WITH NAME = '', DESCRIPTION = '', ERASEFILES = 3, LOGTO = '\\hbhscorp4\sqlbak\sqlcmsbk\Admin_TLog_20050615_152007.Log', COMPRESSION = 1 6/15/2005 3:20:19 PM: 6/15/2005 3:20:19 PM: 6/15/2005 3:20:19 PM: Backup data size : 832.000 KB 6/15/2005 3:20:19 PM: Compressed data size: 12.000 KB 6/15/2005 3:20:19 PM: Compression rate : 98.56% 6/15/2005 3:20:25 PM: Process completed successfully. 6/15/2005 3:20:25 PM: Processed 1 pages for database 'Admin', file 'Admin_Log' on file 1. BACKUP LOG successfully processed 1 pages in 0.099 seconds (0.082 MB/sec). (1 row affected) CMS2000 Log 6/15/2005 3:25:07 PM: SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 6/15/2005 3:25:07 PM: 6/15/2005 3:25:07 PM: Backing up CMS2000 (transaction log) to \\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050615_152433.TRN ... 6/15/2005 3:25:07 PM: 6/15/2005 3:25:07 PM: BACKUP LOG [CMS2000] TO DISK = '\\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050615_152433.TRN' WITH NAME = '', DESCRIPTION = '', ERASEFILES = 3, LOGTO = '\\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050615_152433.Log', COMPRESSION = 1 6/15/2005 3:25:07 PM: 6/15/2005 3:25:07 PM: 6/15/2005 3:25:11 PM: Backup data size : 38.000 MB 6/15/2005 3:25:11 PM: Compressed data size: 8.317 MB 6/15/2005 3:25:11 PM: Compression rate : 78.11% 6/15/2005 3:26:13 PM: Process completed successfully. 6/15/2005 3:26:13 PM: 6/15/2005 3:26:37 PM: Deleting old backup file: \\hbhscorp4\sqlbak\sqlcmsbk\CMS2000_TLog_20050612_112224.TRN Processed 4722 pages for database 'CMS2000', file 'CMS2000_Log' on file 1. BACKUP LOG successfully processed 4722 pages in 3.583 seconds (10.795 MB/sec). (1 row affected) / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
First Profiler trace containing the backup commands... Event Class Application Name Text Data SPID Start Time Audit Logout SQLAgent - Job Manager 68 6/15/05 3:20 PM Audit Login OSQL-32 #NAME? SQL:...
0 votes
In my case I am seeing approx a 2 minute difference between the log reported time and the actual amount of time. My currently group of regular transaction log backups is taking 20-25 minutes using SQLBackup while native it is in the 2-5 minute range. What is the next step for correcting this? / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
In my case I am seeing approx a 2 minute difference between the log reported time and the actual amount of time. My currently group of regular transaction log backups is taking 20-25 minutes using ...
0 votes
The log states the backup takes 3 seconds, while actually timing the backup it takes 2-3 minutes. I am willing to try backing up localy, but that is not a fix for this issue. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
The log states the backup takes 3 seconds, while actually timing the backup it takes 2-3 minutes. I am willing to try backing up localy, but that is not a fix for this issue.
0 votes
So what is the next step for solving this? Native backups to network shares don't have this issue. In fact native transaction log backups currently complete 2x-3x faster than compressed ones. FYI - Server is a quad 1.4Ghz with an average CPU utilization while backups are occuring of 15-30%. / comments
  1. Community
  2. SQL Backup Previous Versions
  3. Slow Transaction log backup performance
So what is the next step for solving this? Native backups to network shares don't have this issue. In fact native transaction log backups currently complete 2x-3x faster than compressed ones. FYI -...
0 votes
Total activity
23
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
3