j_baldwinson (8/10/2011)
I was expecting the fragmentation to grow on the basis that pages at the end of a database file get moved to empty pages earlier during a shrink.When i tried this out with a new database (512mb data file and 128mb log file) I got a different fragmentation of 46.875% (2008) 70.3125% (2005) before the table drop and the same after the shrink. I have tried it several times on SQL 2005 (9.00.5057) and SQl 2008 (10.50.2500) and get roughly the same fragmentation percentage. During the shrink i got the warning mesage below.
DBCC SHRINKDATABASE: File ID 1 of database ID 34 was skipped because the file does not have enough free space to reclaim.
I tried running the script with 2500 inserts into FirstTable and got exactly the same frgmentation as the question 39.0625% but still got the same warning when trying to shrink the file.
So the correct answer for me was no change. I guess the real answer is "it depends" on your setup.
Your database didn't get shrunk because the space for FirstTable didn't get released for some reason. Try to use CHECKPOINT between them and see if you get a different result.
The point with the question is to demonstrate the issue with shrink and why you should avoid it.
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform