May 11, 2012 at 2:33 pm
this comes to the differences of creating a database by a script vs SSMS. we are moving servers and when moving to the new server we are making all our file sizes where we want them by shrinking or enlarging them. we came across a database that one of our developers created with:
CREATE DATABASE SomeName
( NAME = 'SomeName', FILENAME = N'SomeName.mdf' , SIZE = SomeSizeKB , MAXSIZE = UNLIMITED, FILEGROWTH = SomeSizeKB )
LOG ON
( NAME = 'SomeName_log', FILENAME = N'SomeName_log.ldf' , SIZE = SomeSizeKB , MAXSIZE = SomeSizeGB , FILEGROWTH = SomeSizeKB )
GO
of course since we cant shrink below the original size of the log file SQL Server yelled at us. (the DB was set to full when it only needed simple and so the log grew to an ungodly size). the databases created in SSMS did not have this issue.
the question i have is what does SSMS do differently from the create database script? I know when SSMS tries to alter a table it copies the data to a temp table then drops the original and creates a new table and transfers every thing back so SSMS must be doing something different when creating the database.
EDIT: forgot a couple of ' in my sanitizing
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 11, 2012 at 4:15 pm
capn.hector (5/11/2012)
this comes to the differences of creating a database by a script vs SSMS. we are moving servers and when moving to the new server we are making all our file sizes where we want them by shrinking or enlarging them. we came across a database that one of our developers created with:
CREATE DATABASE SomeName( NAME = 'SomeName', FILENAME = N'SomeName.mdf' , SIZE = SomeSizeKB , MAXSIZE = UNLIMITED, FILEGROWTH = SomeSizeKB )
LOG ON
( NAME = 'SomeName_log', FILENAME = N'SomeName_log.ldf' , SIZE = SomeSizeKB , MAXSIZE = SomeSizeGB , FILEGROWTH = SomeSizeKB )
GO
of course since we cant shrink below the original size of the log file SQL Server yelled at us. (the DB was set to full when it only needed simple and so the log grew to an ungodly size). the databases created in SSMS did not have this issue.
the question i have is what does SSMS do differently from the create database script? I know when SSMS tries to alter a table it copies the data to a temp table then drops the original and creates a new table and transfers every thing back so SSMS must be doing something different when creating the database.
EDIT: forgot a couple of ' in my sanitizing
You kind of lost me a little as to whether you're concerned about shrinking a log file or creating a new database.
On the create database aspect both methods, the SSMS New Database dialog and the CREATE DATABASE T-SQL statement, take their Recovery Model (amongst other options) from model. As for what each does different, when defining a new DB using the GUI SSMS and scripting it out SSMS will generate ALTER DATABASE commands for every option you can set in the New Database dialog to make sure that if model is changed between the time you script it and the time you run it that you get what you asked for.
Did the developer script include anything after CREATE DATABASE? Is model setup the same across the instances where the database was created originally and where it was hosted, or will be hosted, possibly creating some confusion about how the database ended up in FULL recovery?
Hope that helps, or at least furthers the conversation.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 5:00 pm
there were other options set. the developer scripted out the dev database and ran the script on the production server. since we dont need it as large as it was we wanted to shrink the log file just before we move to a new server. then take a backup and restore undoing some of the bad effects of the shrink. after wondering why we could not shrink the log file further we realized the file was created by the script with an initial size of what it shrank to. when we created a database using the SSMS GUI Wizard we could shrink the log file smaller than the "Initial" size set durring the wizard and either the question has never come up or our google fu is weak on this one.
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 11, 2012 at 5:35 pm
OK, I am with you now. I would run a Trace to see what SSMS is doing. My guess is SSMS does a SHRINKFILE w/ NOTRUNCATE first to bring all used pages to the front of the file before trying to actually shrink the file.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 5:49 pm
opc.three (5/11/2012)
OK, I am with you now. I would run a Trace to see what SSMS is doing. My guess is SSMS does a SHRINKFILE w/ NOTRUNCATE first to bring all used pages to the front of the file before trying to actually shrink the file.
after reading both my replies its a little hard for me to follow as well and i appologize for that.
actually im wondering why i can shrink the database log CREATED by SSMS and cannot shrink the database log created by the script. once we realized the dev did this we tested our theory. created a db with the SSMS GUI and a DB with a CREATE DATABASE statement for testing.
both using DBCC SHRINKFILE. with the db by script we get the "This file can not be shrunk any more" output where as with the db created by ssms we can shrink it as small as we want. im wondering how SSMS sets the file sizes. does is set the DB as 1MB then grow the files to the "Initial" Size?
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 11, 2012 at 5:53 pm
Could be. I did not think SHRINKFILE was sunject to the 'initial size' issue though.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 11, 2012 at 6:07 pm
opc.three (5/11/2012)
Could be. I did not think SHRINKFILE was sunject to the 'initial size' issue though.
i believe only when used with CREATE DATABASE. all the DB's we created with the GUI wizard we could shrink below the "Initial" size of the log file when we created them. any with the CREATE DATABASE sent through the query window we could not shrink lower.
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 11, 2012 at 8:40 pm
capn.hector (5/11/2012)
opc.three (5/11/2012)
Could be. I did not think SHRINKFILE was sunject to the 'initial size' issue though.i believe only when used with CREATE DATABASE. all the DB's we created with the GUI wizard we could shrink below the "Initial" size of the log file when we created them. any with the CREATE DATABASE sent through the query window we could not shrink lower.
I was thinking of data files with the NOTRUNCATE comment...but we're talking log files so please disregard that line of thinking.
What message do you receive when the shrink fails?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 9:25 am
opc.three (5/11/2012)
capn.hector (5/11/2012)
opc.three (5/11/2012)
Could be. I did not think SHRINKFILE was sunject to the 'initial size' issue though.i believe only when used with CREATE DATABASE. all the DB's we created with the GUI wizard we could shrink below the "Initial" size of the log file when we created them. any with the CREATE DATABASE sent through the query window we could not shrink lower.
I was thinking of data files with the NOTRUNCATE comment...but we're talking log files so please disregard that line of thinking.
What message do you receive when the shrink fails?
Cannot shrink log file 2 (SomeName_LOG) because all logical log files are in use. we fixed it by detaching the database with sp_detach_db rename the log file then sp_attach_single_file_db. it worked and we can now shrink the file. when i get some time ill set up a trace and create a database with ssms to answer the question of what tsql ssms runs. might be later this week though. ill post the results here.
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 14, 2012 at 9:31 am
capn.hector (5/14/2012)
opc.three (5/11/2012)
capn.hector (5/11/2012)
opc.three (5/11/2012)
Could be. I did not think SHRINKFILE was sunject to the 'initial size' issue though.i believe only when used with CREATE DATABASE. all the DB's we created with the GUI wizard we could shrink below the "Initial" size of the log file when we created them. any with the CREATE DATABASE sent through the query window we could not shrink lower.
I was thinking of data files with the NOTRUNCATE comment...but we're talking log files so please disregard that line of thinking.
What message do you receive when the shrink fails?
Cannot shrink log file 2 (SomeName_LOG) because all logical log files are in use. we fixed it by detaching the database with sp_detach_db rename the log file then sp_attach_single_file_db. it worked and we can now shrink the file. when i get some time ill set up a trace and create a database with ssms to answer the question of what tsql ssms runs. might be later this week though. ill post the results here.
That makes sense. You cannot shrink a log to smaller than the first Virtual Log file's size. Recreating the DB with no log should make a 1MB log file (I think).
As for how the file is created between GUI and T-SQL, I do not think it has much to do with it as long as all settings are equal. It has to do with the initial size, because that is what determines how SQL Server brings up the initial Virtual Log file.
This article on the size of virtual fog files SQL Server will create in relation to the size of the log file itself might help clear things up: Transaction Log VLFs - too many or too few?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 9:52 am
the VLF's being used was what tipped us to the script being used and having 2 VLF's at the size of Initial / 2. thanks for the article by kimberly. ill deffinitly have to look into optimizing the log file in the future.
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 14, 2012 at 11:49 am
capn.hector (5/14/2012)
we fixed it by detaching the database with sp_detach_db rename the log file then sp_attach_single_file_db. it worked and we can now shrink the file.
Bad idea generally.
A quick check with DBCC LOGINFO() would have provided valuable info, you could also check the free space inside the file using
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed')
AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 14, 2012 at 12:04 pm
Perry Whittle (5/14/2012)
capn.hector (5/14/2012)
we fixed it by detaching the database with sp_detach_db rename the log file then sp_attach_single_file_db. it worked and we can now shrink the file.Bad idea generally.
A quick check with DBCC LOGINFO() would have provided valuable info, you could also check the free space inside the file using
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed')
AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files
while i agree its a bad idea in general in this case it was the only way. i was wrong on the error though. durring my testing i recreated the error which is as follows:
Cannot shrink log file 2 (test2_log) because total number of logical log files cannot be fewer than 2.
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. 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.
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 14, 2012 at 1:06 pm
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?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 1:36 pm
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.
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 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply