Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrink Database


Shrink Database

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
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).
Thomas Abraham
Thomas Abraham
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 2254
Looks like many of us, judging from the distribution of answers, learned something today. Thanks for the question.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
SickThis 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
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
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? ;-)
jbreffni
jbreffni
SSChasing Mays
SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)SSChasing Mays (619 reputation)

Group: General Forum Members
Points: 619 Visits: 266
I got 0.46875 when I ran the script.

What's up with that?



Britt Cluff
Britt Cluff
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 253
Took a guess and got it wrong. I don't have much experience with shrinking databases and had no idea it would cause that much fragmentation.

http://brittcluff.blogspot.com/
Toreador
Toreador
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2415 Visits: 8064
SanDroid (8/10/2011)
<lots of stuff>


and breathe....
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Toreador (8/10/2011)
SanDroid (8/10/2011)
<lots of stuff>


and breathe....


Hehew00t:-D
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
hakan.winther (8/10/2011)
I am surprised that 60% thought it wouldn't increase!


If the default behavior of dbcc shrinkdatabase was the truncate_only option wouldn't it stay the same? That's what my initial thought was but I ended up changing it to about 50% based on the fact that were too tables. Good question.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36146 Visits: 18751
Excellent question, Ron. I've used this type of demo in presentations before.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search