Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2010-05-13 : 10:44:30
|
| Hi All. Can someone clear up for me how SQL backups handle allocated but unused space. (cannot find any decent information on the web)There is a performance impact to grow and shrink data sizes, so why when I shrink a DB of 70GB of unused space then perform a backup it was took the same time and was identical size as before?Any ideas? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-13 : 12:11:05
|
| Unused space is not backed up (well, you might have pages that are sparsely populated, and thus an index rebuild or somesuch [at the most extreme: a complete data export, cleardown and reimport] might "pack" the data more tightly, and thus reduce the backup size)You should not use SHRINK as it will cause fragmentation and lead to reduced performance. (Exception being a one-time shrink after a one-time delete of stale data, or a one-time-shrink to get the database back to "normal size" after curing some uncontrolled-growth) |
 |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2010-05-13 : 12:22:32
|
| Appreciate the clarification.sp_spaceused shows the unused space in minimal. I was thinking from backup costs view point. All good then Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-13 : 12:29:15
|
| One possible cost (although I think sovled in SQL 2005l, and thus demoted to ancient memory of SQL 2000!) is to do with restoring a backup which requires that the database is pre-created to the size as per the backup-file's source-database.In SQL 2000 (and I think its solved now) this took ages, and thus there may have been a case for trying to keep database size "down"There is still an issue that if you have to restore, in extremis, onto a spare box you would need enough space equivalent to the original database's MDF + LDF files. But I don't think there is a performance implication of creating an overly large DB in SQL2005 onwards.Anyways, I recommend avoiding SHRINK like the plague - as best as you can. |
|
|
|
|
|
|
Allocated Unused Space - SQL Backups