January 14, 2015 at 3:41 pm
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
January 14, 2015 at 4:21 pm
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".
January 14, 2015 at 4:25 pm
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?
January 14, 2015 at 4:28 pm
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".
January 14, 2015 at 4:39 pm
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.
January 15, 2015 at 4:20 am
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
January 15, 2015 at 3:36 pm
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