<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by bitbucket
»
Shrink Database
72 posts, Page 2 of 8
««
1
2
3
4
5
»
»»
Shrink Database
Rate Topic
Display Mode
Topic Options
Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, August 10, 2011 7:09 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 21,357,
Visits: 9,532
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
Thomas Abraham
Thomas Abraham
Posted Wednesday, August 10, 2011 7:49 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 1,146,
Visits: 1,448
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.
Post #1157619
SanDroid
SanDroid
Posted Wednesday, August 10, 2011 8:29 AM
Ten 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
SanDroid
SanDroid
Posted Wednesday, August 10, 2011 8:35 AM
Ten 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
jbreffni
jbreffni
Posted Wednesday, August 10, 2011 8:41 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 6:15 AM
Points: 554,
Visits: 176
I got 0.46875 when I ran the script.
What's up with that?
Post #1157682
Britt Cluff
Britt Cluff
Posted Wednesday, August 10, 2011 8:47 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,558,
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
Toreador
Toreador
Posted Wednesday, August 10, 2011 8:51 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 1,356,
Visits: 4,761
SanDroid (8/10/2011)
<lots of stuff>
and breathe....
Post #1157695
Ninja's_RGR'us
Ninja's_RGR'us
Posted Wednesday, August 10, 2011 8:54 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 21,357,
Visits: 9,532
Toreador (8/10/2011)
SanDroid (8/10/2011)
<lots of stuff>
and breathe....
Post #1157702
cfradenburg
cfradenburg
Posted Wednesday, August 10, 2011 9:15 AM
SSCommitted
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:44 AM
Points: 1,555,
Visits: 1,925
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Wednesday, August 10, 2011 9:24 AM
SSC-Dedicated
Group: Administrators
Last Login: 2 days ago @ 1:47 PM
Points: 31,406,
Visits: 13,722
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 »
72 posts, Page 2 of 8
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
