SQL Server 2008 onwards we have a new feature which allows us to generate compressed backup. This feature is a part of Enterprise edition in SQL 2008 and available in SQL 2008 R2 Standard and Enterprise edition.
We will see how compression backup will works, when you run backup database command using compression option SQL server engine will think how much space it is required for backup and occupies that much space on the filesystem, if the backup is not fit with in the allocated space then it will simply extended to file size. If the backup completes and not used the pre-allocated space then it simply shrinks the file in the disk. The pre-allocation reduces the IO overhead rather constantly growing the file size while backup is running.
Most of the times the pre-allocated space is larger than the actual backup size. For example if your disk is low and you initiate a large backup backup may fail with insufficient disk space. If you want to switch off the pre-allocated space by SQL Server then you can use 3042 trace flag, enabling this trace flag will tells the database engine not to pre-allocate file size and just grow the file while backup is running. But there will be a little performance hit when you enable the trace flag
No comments: