October 4, 2010 at 12:10 pm
Anyone know how Winrar deals with the unused portion of an mdf data file (SQL Server Version 2005) when compressing the file? I have to provide on a regular basis, a copy of our DB to our primary vendor for support and everytime I prep the thing to send I can't help but wonder if it might go faster or be more effecient if the mdf file did not have a sizeable chunk of unused space in it.
The space is there for growth which makes sense for the DB when dealing with the live copy but in a backup copy being transmissioned over the internet (via FTP after being compressed by WinRar) I wonder if WinRar is seeing that unused space as if it were empty and therefore compressing it a LOT or if its seen as adata like any other part of the file. Any ideas or thoughts on how WinRar or any other compression tool handles the white space of a DB File?
Thanks
Kindest Regards,
Just say No to Facebook!October 4, 2010 at 12:48 pm
I am no expert on compression. 7-zip[/url] does a great job when it comes to compression and it is open source too.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 4, 2010 at 1:15 pm
How does the size of the .rar file compare to the size of the .mdf file?
Wouldn't it be simpler to provide your support vendor with a copy of a database backup file that you have compressed? If you have a command line rar utility, you could even setup a SQL Server job to do the compression.
October 4, 2010 at 1:22 pm
Depends what that unused space looks like. If it once was part of a table, there will still be values on those pages. SQL doesn't zero out a page once it's deallocated. Hence to any compression tool outside of SQL, that space will likely look much the same as the allocated space.
Now, if you were talking about a backup, it would be another story. SQL doesn't back up unallocated pages, hence only the used portion of the database would be in the backup file in the first place
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2010 at 2:04 pm
Gail,
Well it is a restore from a backup but not a native SQL Backup. We use DPM (from Microsoft) and so the normal laws of the SQL World seem to get warped when dealing with DPM. It works great and is easy to use even for real time recover and restore of log file backups but its not flexable at all. I can't say with certainty that the same DB restored by DPM and another using Native SQL Back & Restore (from the same source) woudl in fact be the same.
Currently the compression I get out of Winrar is excellent with it averaging a ratio of about 20 to 1. So the final rar file is not that big but it does take a while to create and so I'm always looking for ways to shorten the duration for the process. THose who want the DB backup sent off to the Vendor don;t seem to understand why it can take a few days to go from backup (on DPM) to the vendor having recieved all 20GB of the compressed DB.
Thanks
Kindest Regards,
Just say No to Facebook!October 4, 2010 at 2:53 pm
I am no expert on compression. 7-zip does a great job when it comes to compression and it is open source too.
I've found 7-zip to pretty much be the best at compressing a file as small as possible. But I've also found it to be about the slowest.
The Redneck DBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply