Read It (3/29/2011)
I liked the article. I'm a bit confused by your numbers though. You said make note of the original data and log file sizes and then show the before and after file sizes after the delete was performed. There's a significant difference in the original file sizes to the before and after file sizes. I'm not sure what's happening there. Can you give me a little more explanation on those?
Hello!
Good to know that you liked reading my article. I can definitely help you out in understanding the difference in the file sizes. Please find the explaination below:
Case #1 - Deleting Random Data from a table
Initially, we generated our test data, and noted the file sizes. The data and log files came out to 2694MB and 1705MB respectively.
Next, we generated the lookup table, and then executed the purge. The "Before" and "After" values are with respect to the Purge operation, and hence contain the space occupied by the lookup table.
The above also applies to Case #2 - Deleting data from a heap (non-clustered table).
The basic point I was trying to make is that the file sizes remain constant during the purge operation, and hence I have taken the file size measurements accordingly.
Do let me know if you still have any doubts, and I will be more than happy to help you out.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins