Backups have more than doubled in size

  • I'm using native SQL Server backups for 2 SQL Server 2008 R2 instances (prod and dev). Last night I had backups jump in size. On production the backup went from 8 to 20 GB and on dev the backup went from 7 to 17 GB.

    I'm using full recovery and transaction log backups every 10 minutes in production.

    Using the query below I looked at total row count and total spaced from the current databases and copies restored from the smaller backups. I see only a small change in size, 700 MB in prod and 12 MB in dev.

    What could have caused such a spike in backup size and are there any ways to see what might have caused it?

    Thanks,

    Tim

    SELECT

    t.NAME AS TableName,

    s.Name AS SchemaName,

    p.rows AS RowCounts,

    SUM(a.total_pages) * 8 AS TotalSpaceKB,

    SUM(a.used_pages) * 8 AS UsedSpaceKB,

    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

    FROM

    sys.tables t

    INNER JOIN

    sys.indexes i ON t.OBJECT_ID = i.object_id

    INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

    INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

    LEFT OUTER JOIN

    sys.schemas s ON t.schema_id = s.schema_id

    WHERE

    t.NAME NOT LIKE 'dt%'

    AND t.is_ms_shipped = 0

    AND i.OBJECT_ID > 255

    AND p.rows > 0

    GROUP BY

    t.Name, s.Name, p.Rows

    ORDER BY

    SUM(a.total_pages) desc

  • Were the backups using compression before and are did not use compression last time for some odd reason?

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • No, I didn't turn off compression or make any other changes to the maintenance jobs. Is there any way to query a backup file to see if it used compression during the backup process?

  • T_Peters (1/14/2015)


    No, I didn't turn off compression or make any other changes to the maintenance jobs. Is there any way to query a backup file to see if it used compression during the backup process?

    Indirectly.

    In the msdb.dbo.backupset table, there are separate columns:

    backup_size and

    compressed_backup_size

    If they are different, compression was used; if not, it wasn't. [AFAIK, there is no actual flag that "says" "compression was specified on this backup".]

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks, that was useful. I did find my production differentials were not compressing. The production full as well as the dev backups were compressed. Still not sure what caused the spike.

  • T_Peters (1/14/2015)


    No, I didn't turn off compression or make any other changes to the maintenance jobs. Is there any way to query a backup file to see if it used compression during the backup process?

    You can use RESTORE HEADERONLY to look at information about the backup, how it was taken, what it contains, etc. I wrote an article[/url] about all the various ways to get information right out of the backups themselves.

    Nothing else has changed anywhere on the system? No network changes, SAN, NAS, storage, service packs, nothing? Since you're seeing the doubling in two servers, are these servers regularly sharing data? If a large data spike occurred in prod, would it immediately go to dev? If not, then you're looking at something systemic around your servers.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • There isn't any automated transfer of data between the two databases.

    I'm guessing it has something to do with the SAN destination of the backups. Today I was seeing errors like "Extend Disk Backup: failure on backup device...Operating system error 112(There is not enough space on the disk.)." for a ~8KB t-log backup. There was enough space for these and more. I could do a full backup locally and then copy the completed back up over to the SAN. I've switched over to backing up locally until the SAN admin gets back in town from vacation :(. The local backups are back to the usual size.

    Tim

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply