Hugo Kornelis (4/23/2010)
The log file will contain only the fact that page so-and-so was deallocated, but the log backup (if one is taken) will also include a copy of that page.
I'm not sure whether I'm misunderstanding you, but to my mind this makes no sense (and does not correspond to my practical experience):
- Use a DB in a test environment where you can mess with the data and backups, and let's assume the DB is set to is fully logged.
- Fill a table with a couple GBs of data (using your favorite data-generation method)
- Checkpoint, just to be safe
- Backup the transaction log (ignore this backup file - if you like you can use WITH NO_LOG/TRUNCATE_ONLY, we don't need log chain continuity)
- Shrink the transaction log
-> the transaction log is down to a few MB in size
- Truncate the large table with all that test data.
- Checkpoint, just to be safe
- Back up the transaction log
-> take a look at the size of the transaction log backup... a few MB in size?
I must admit I have not followed these explicit steps in preparation for this post, but does anyone expect behaviour different from this? (does anyone expect the transaction log backup to contain copies of the deallocated pages??)
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.