May 14, 2012 at 1:47 pm
capn.hector (5/14/2012)
opc.three (5/14/2012)
durring the server trace i could not find any differences in the T-SQL being executed between the create database script vs SSMS Create DB Wizard.
Thank goodness. I think that was why it was so hard to figure what you were pointing out. If this were not true it would have made a lot of people's worlds come crashing down, including mine 😛
the difference in the log file however was using the create script (ran from the query window) there were only ever 2 VLF's in the log file each with a size of 1/2 Initial. with SSMS there were multiple VLF's in the numbers mentioned in Kimberly's article linked above.
Still not sure why but now im even more curious as to how SSMS causes the multiple VLF's to be created.
Same initial log sizes for both? i.e. same exact SQL generated by GUI as what was in Dev's script?
yep same initial log sizes. ill really dig into it as now it has me curious. but at least i can focus in on a section now. figure out why SSMS is able to create Multiple VLF's vs the 2 when running the DDL directly. might have the makings of my first article if some one does not beat me to it.
It would make a good article if you can isolate the difference in behavior, recreate it and manipulate it for a demo. It may be worth looking for, or opening a Connect item too. It may be a known issue but I would expect you would see any differences in the Trace. This one concerns me :ermm:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 2:58 pm
opc.three (5/14/2012)
capn.hector (5/14/2012)
opc.three (5/14/2012)
durring the server trace i could not find any differences in the T-SQL being executed between the create database script vs SSMS Create DB Wizard.
Thank goodness. I think that was why it was so hard to figure what you were pointing out. If this were not true it would have made a lot of people's worlds come crashing down, including mine 😛
the difference in the log file however was using the create script (ran from the query window) there were only ever 2 VLF's in the log file each with a size of 1/2 Initial. with SSMS there were multiple VLF's in the numbers mentioned in Kimberly's article linked above.
Still not sure why but now im even more curious as to how SSMS causes the multiple VLF's to be created.
Same initial log sizes for both? i.e. same exact SQL generated by GUI as what was in Dev's script?
yep same initial log sizes. ill really dig into it as now it has me curious. but at least i can focus in on a section now. figure out why SSMS is able to create Multiple VLF's vs the 2 when running the DDL directly. might have the makings of my first article if some one does not beat me to it.
It would make a good article if you can isolate the difference in behavior, recreate it and manipulate it for a demo. It may be worth looking for, or opening a Connect item too. It may be a known issue but I would expect you would see any differences in the Trace. This one concerns me :ermm:
well more testing and on my 2008 R2 install on my local box its working just fine both ways creating the correct number of VLF's for files over 2 gig. might be an 08 specific issue but cant test on the production box. this one has me really confused but ill keep testing and ill get the environment as close as i can to the original problem so i can run the exact script that was run on or old production server and let you guys know whats up. :crazy:
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 17, 2012 at 2:36 pm
After much testing and looking at VLF sizes and rerunning every query i found the create script created 16 VLF's and all the sizes we tried to shrink to were larger than 15 VLF's (meaning we could not shrink it). the DB's created with ssms had a much more optimized size of the VLF's and had many more of them. was a great learning experience and a bit of a facepalm when i realized what was going on.
also taught me allot about log files.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply