Sql-server – Why does backup file size become enormous when Agent schedule is changed

backupsql serversql-server-2012

I have a database that is currently backed up every two hours. This is a secondary backup, done in addition to the hosting provider's backup strategy. I created a procedure (stored within master) to perform the following actions on a scheduled basis in SQL Agent:

  1. If the backup file doesn't exist create it and perform a full (copy only) backup
  2. If the backup file does exist, perform a transaction log backup

The code creates a backup file with a weekly date stamp (e.g. 2017_Week37_myDbName.bak), then drops and re-creates a new file each week.

The code is currently scheduled to run every two hours, which for my level of understanding meant I would have a maximum of two hours data loss in the event of failure. The initial backup file created in step 1 is 230MB, which obviously increases every two hours as the transaction log backup is added. Each week, the file reaches a size of around 7GB, before being deleted by the procedure.

I then changed the schedule to run every 15 minutes, but noticed that the backup file went over 230GB – a gigantic increase. There may be a bug (which I cannot figure), but I'm wondering why this would happen? I thought that the transaction log backup would only contain the transactions backed up since the last backup, so regardless of the frequency, the backup file would always be a similar size before it was deleted. I should clarify that at no point was the database used any more frequently than normal. Basically, I am trying to create differential-style backups after the initial full backup.

CREATE PROC [dbo].[BackupMyDB]

    @myDbName nvarchar(50)

AS

    DECLARE @year nvarchar(4) = CAST(YEAR(GETDATE()) AS nvarchar);
    DECLARE @weekNumber nvarchar(2) = CAST(RIGHT('0' + RTRIM(DATEPART(wk, getdate())), 2) as nvarchar);
    DECLARE @backupFileName nvarchar(max) = @year + '_Week' + @weeknumber + '_' + @mydbname; -- '2013_Week37_myDbName'
    DECLARE @backupPhysicalPath nvarchar(100) = N'C:\SqlScheduledBackups\';
    DECLARE @fullPath nvarchar(150) = @backupphysicalpath + @backupFileName;
    DECLARE @backupName nvarchar(200);

    IF NOT EXISTS (SELECT * FROM sys.backup_devices WHERE (name=@backupFileName))
        BEGIN

            -- create backup device and perform full backup
            EXEC master.dbo.sp_addumpdevice  @devtype=N'disk', @logicalname=@backupFileName, @physicalname=@fullpath;
            SET @backupname = @myDbName + '-Full Database Backup';
            BACKUP DATABASE @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, RETAINDAYS=45, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;

            -- delete the previous backup file
            DECLARE @previousBackupName nvarchar(200);
            SELECT
                  TOP 1 @previousBackupName = o.name
            FROM
                sys.backup_devices o
            WHERE
                (o.name <> @backupfilename) AND
                (o.name LIKE @year + '_Week_%_' + @mydbname)
            ORDER BY
                o.name ASC;

            IF @previousBackupName IS NOT NULL
                BEGIN
                    EXEC master.dbo.sp_dropdevice @logicalname = @previousBackupName, @delfile='delfile';
                END
        END
    ELSE
        BEGIN
            SET @backupname = @myDbName + '-Transaction Log Backup';
            BACKUP LOG @mydbname TO  @backupFileName WITH COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT,  NAME=@backupname, SKIP, NOREWIND, NOUNLOAD, STATS=10;
        END
GO

Best Answer

You don't indicate how often your provider issues BACKUP LOG commands (which I assume are not COPY_ONLY), but here is my guess at to what is happening.

Let's assume a 'regular' transaction log backup has occurred at 8am which truncates the log. Also, let's assume that 1gb of transaction log is being created every 15 minutes.

  • 8:00am - Your BACKUP LOG COPY_ONLY runs and doesn't really backup anything because the log has just been truncated by the normal log backup.
  • 8:15am - Your BACKUP LOG COPY_ONLY runs and backs up up everything since 8:00am (because that's when the log was last truncated). Let's say 1gb.
  • 8:30am - Your BACKUP LOG COPY_ONLY runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 2gb.
  • 8:45am - Your BACKUP LOG COPY_ONLY runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 3gb.
  • 9:00am - Your BACKUP LOG COPY_ONLY runs and backs up everything since 8:00am (because that's when the log was last truncated) - now 4gb

You are also 'appending' this data to your existing backup because you are using NOINIT.