Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Shrink Database Expand / Collapse
Author
Message
Posted Wednesday, August 10, 2011 7:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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).
Post #1157585
Posted Wednesday, August 10, 2011 7:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 1,621, Visits: 2,031
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
Post #1157619
Posted Wednesday, August 10, 2011 8:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.

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. 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?!

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.
Post #1157663
Posted Wednesday, August 10, 2011 8:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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?
Post #1157674
Posted Wednesday, August 10, 2011 8:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:05 AM
Points: 581, Visits: 221
I got 0.46875 when I ran the script.

What's up with that?



Post #1157682
Posted Wednesday, August 10, 2011 8:47 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
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/
Post #1157689
Posted Wednesday, August 10, 2011 8:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 1,658, Visits: 6,001
SanDroid (8/10/2011)
<lots of stuff>


and breathe....
Post #1157695
Posted Wednesday, August 10, 2011 8:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Toreador (8/10/2011)
SanDroid (8/10/2011)
<lots of stuff>


and breathe....


Post #1157702
Posted Wednesday, August 10, 2011 9:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
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.
Post #1157724
Posted Wednesday, August 10, 2011 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
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
Post #1157736
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse