August 9, 2011 at 8:23 pm
Comments posted to this topic are about the item Shrink Database
August 9, 2011 at 11:06 pm
Great question.Learned new thing today.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
August 10, 2011 at 1:05 am
Great article by Paul. Good question, like to see more of these.
M&M
August 10, 2011 at 2:12 am
I am surprised that 60% thought it wouldn't increase! Shrinking a databases is like the opposite of defragmentation! I have seen some terrible examples at some clients using "auto shrink" on the databases. Guess what happens to the performance!
Shrinking a database would give you a warning about the fragmentation issues, and auto shrink would send a message to your boss telling him to fire you! 🙂
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
August 10, 2011 at 2:50 am
I didn't have the foggiest, so am one of the 60% who guessed wrongly that it wouldn't increase!
I've never had to work with a database that did anything other than grow 🙂
August 10, 2011 at 4:11 am
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.
August 10, 2011 at 5:24 am
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
August 10, 2011 at 6:08 am
Very nice question @bitbucket-25253.
I got it wrong because I couldn't choose between 50% and 90%. I knew the heap writes all over the place and that shrink "usually" drives fragmentation over 90%. However in this case I was struggling with the fact that there's only 2 tables. Given that only 1 table was left I was think that all the page would move to the front an be "somewhat" in order.
Loved this question, just giving you pointers for the next ones :-).
August 10, 2011 at 6:36 am
obviously a very worthwhile educational question on the basis that so many people have thought the shrink would have no affect.
now they know!
---------------------------------------------------------------------
August 10, 2011 at 7:03 am
I knew it would screw up the database.... didn't expect 90% but figured what the hey and got it right.
What I found more interesting was being able to predict the initial fragmentation. Is there a magical formula for that somewhere?
August 10, 2011 at 7:09 am
mtassin (8/10/2011)
I knew it would screw up the database.... didn't expect 90% but figured what the hey and got it right.What I found more interesting was being able to predict the initial fragmentation. Is there a magical formula for that somewhere?
It's the query that uses the dmv (with limited parameter).
August 10, 2011 at 8:29 am
:sick:This is ABSOLUTELY a depends trick question. It ABSOLUTELY depends on if you correct the error in the script that was listed. Also your DB settings change the results. Please next time include the DB creation script or at least let us have a clue about the DB settings. Also do not include a script that changes the answer completely if you remove the error including the TABLE CREATE in the first batch of 1280 executions.:-P
Here is how to run the script and get a "WRONG" answer every time.
On a 2005 SQL server with DB recovery set to simple or FULL run the script exactly as listed in the QOTD.
This might be because of the way the First table create statement is included in the "GO 1280" batch. This would definately change the way the logging could occur. So after seeing i got the wrong Guess or Answer I changed the logging to FULL and rand the script again. This time there was only a slight increase in the Fragmentation. Another differant RIGHT wrong answer.
So now I am very interested. Same script same server same database. Only I fix the script problem with the first table create being included in the "GO 1280" batch.
Finally I get some Fragmentation. Is this really Table or DB file fragmentation though. Let's set the DB back to Simple recovery mode, drop both tables, Backup DB, Shrink the DB and find out what we get.
The Second table has the 0.390625 avg frag percentage before the drop and shrink. It has 15.625 after...
Now I take back out my correction. Were I added the missin GO after the "CREATE TABLE FirstTable" of the script and try again.
MAGIC! The Fragmentation is the same before and after SHRINKDATABASE!
I leave the mistake in and try again. This time will FULL recovery. Again MAGIC! The Fragementation is the same before and after SHRINKDATABASE!
I know that with FULL recovery (aka Big tran logs)fragmentation can come from repeated running of the same sripts. So reset by dropping secondtable and running SHRINKDATABASE, then run again with the error in the script.
Again same Average fragmentation both times.
I try once more to get the "Correct" result using the EXACT SCRIPT in the QOTD.
This time I do not reset. I do not drop secondtable or run SHRINKDATABASE.
MAGIC AGAIN! Exact Same or less than 2% change of fragmentation every time after six runs.
So finally wanting to try to get the same results as QOTD author was guessing we would get, I reset again. This time removed the error by adding the missing GO after the CREATE TABLE FirstTable script.
Now I get the "RIGHT" answer. :sick: Great another QOTD where you have to guess at the answer. Is it the one if you leave the script alone or the one where you don't?! :w00t:
So Long story short anyone that carefully read the script or test run it would have selected the RIGHT answer, even thought the QOTD says it is wrong.:cool:
August 10, 2011 at 8:35 am
O yeah.. Since over 77% of those that answered today obviously had the same issue with the same mistake in the script I am sure we would all like our point.
Why should my QOTD averages suffer becuase of someones untested trick question script? 😉
August 10, 2011 at 8:41 am
I got 0.46875 when I ran the script.
What's up with that?
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply