Backup Not Compressing
-
On a SQL 2012 Enterprise (SP2) instance, I have a database that is 12gb. The size of its backup file is 10gb. With compression I was expecting the backup size to be around 4gb. This database was migrated from SQL 2005 along with other databases that seem to be compressing ok. The instance is set to compression as the default. The database is not encrypted. It contains mainly character data. Compression is also specified in the maintenance plan. Have also trying running backup as a script. The instance is hosted on a virtual server (Windows Server 2012 R2).
Any thoughts on why this backup is not compressing? Any one else experiencing this?
Thanks.
John
-
I've seen some DBs that don't compress as much as I might have hoped due to GUIDs and encrypted columns, and even some that store data as text that's been encypted before insert.
Any use ?
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font] -
TDE enabled?
-
check for any type of LOB columns, as these do not compress well, if at all.
This should help identify any large columns
select object_name(c.object_id) as tablename,c.name as columnname
from sys.all_columns c inner join sys.all_objects o
on c.object_id = o.object_id
where c.system_type_id in (34,99,165,167,231,241) and o.type = 'U'
order by object_name(c.object_id), c.column_id
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
I think everyone else largely has it covered between data types and the possibility of encryption, you may see little to no compression.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I'm curious as to why you expected it to be 4GB. What were you basing that on?
However, assuming you're close to accurate in your assumption, like a couple of the other guys said, compression is vastly different for different DBs. I've seen everywhere from 5% to 97%. It boils down to nothing but your data.
That said, maybe make sure that that maint plan isn't screwing you over. Take a manual backup with compression so you know for a fact it's being compressed and look at the number then.
Here's a query you can use to see your compression ratio.
SELECT
b.database_name 'Database Name',
CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)'
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
ORDER BY
b.backup_finish_date DESC
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
TDE is not enabled.
Ran the query. Thanks Perry. I changed it to add the column type (below). Mostly varchar and nvarchar, but there are a couple columns with the image column type. I assume these are causing it not to compress well?
select object_name(c.object_id) as tablename,
c.name as columnname,
t.name as columntype
from sys.all_columns c inner
join sys.all_objects o on c.object_id = o.object_id
join sys.systypes t on c.system_type_id = t.xtype
where c.system_type_id in (34,99,165,167,231,241) and o.type = 'U'
order by t.name, object_name(c.object_id), c.column_id
-
KenpoDBA,
Basing that on all our other database backups, in 2012 and 2008r2, compressing to a file less than 30% of the database size. I did try running the backup as a script and from SSMS, with the same result. This database is coming right from 2005 so no compression comparison. As stated in my last post, it looks like a couple image type columns could be the culprit.
Thanks.
-
Agreed. Images don't compress. But it depends how much of the DB they make up. But I've all DBs that were pretty much just image repositories and got almost nothing out of them.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
UconnDBA (1/5/2015)
KenpoDBA,Basing that on all our other database backups, in 2012 and 2008r2, compressing to a file less than 30% of the database size. I did try running the backup as a script and from SSMS, with the same result. This database is coming right from 2005 so no compression comparison. As stated in my last post, it looks like a couple image type columns could be the culprit.
Thanks.
yep, images are flat nasty when it comes to compression.
i once looked after an MS Exchange archiving system that used a sql back end database. There were various tables in the database that had email header and body info but the one main table was the attachments table. It was huge, at one point 180GB and growing rapidly, the backup compression was only around 30% IIRC
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply