Creating a new database with limits using SMO

  • Thread starter Thread starter Robinson
  • Start date Start date
R

Robinson

Hi,

Using SMO (VB.NET), I'm creating a new database as shown below. I would
like to change the growth size of the database (to 10%, it's defaulting to
1mb at the moment) and also to set the maximum size I will allow it to grow
to, to 10mb (for testing purposes). How can I do this on the SMO interface?

Thanks for any tips,


Robin




theServer = New Server(Source.Server)

Dim theDatabase As New Database(theServer , newCatalog)

theDatabase.Collation = "SQL_Latin1_General_CP1_CI_AS"
theDatabase.CompatibilityLevel = CompatibilityLevel.Version90
theDatabase.IsFullTextEnabled = False

theDatabase.DatabaseOptions.AnsiNullDefault = False
theDatabase.DatabaseOptions.AnsiNullsEnabled = False
theDatabase.DatabaseOptions.AnsiPaddingEnabled = False
theDatabase.DatabaseOptions.AnsiWarningsEnabled = False
theDatabase.DatabaseOptions.ArithmeticAbortEnabled = False
theDatabase.DatabaseOptions.AutoClose = True
theDatabase.DatabaseOptions.AutoCreateStatistics = True
theDatabase.DatabaseOptions.AutoShrink = True
theDatabase.DatabaseOptions.AutoUpdateStatistics = True
theDatabase.DatabaseOptions.CloseCursorsOnCommitEnabled = False
theDatabase.DatabaseOptions.LocalCursorsDefault = False
theDatabase.DatabaseOptions.ConcatenateNullYieldsNull = False
theDatabase.DatabaseOptions.NumericRoundAbortEnabled = False
theDatabase.DatabaseOptions.QuotedIdentifiersEnabled = False
theDatabase.DatabaseOptions.RecursiveTriggersEnabled = False
theDatabase.DatabaseOptions.BrokerEnabled = True
theDatabase.DatabaseOptions.AutoUpdateStatisticsAsync = False
theDatabase.DatabaseOptions.DateCorrelationOptimization = False
theDatabase.DatabaseOptions.Trustworthy = False
theDatabase.DatabaseOptions.IsParameterizationForced = False
theDatabase.DatabaseOptions.ReadOnly = False
theDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Simple
theDatabase.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple
theDatabase.DatabaseOptions.PageVerify = PageVerify.TornPageDetection
theDatabase.DatabaseOptions.DatabaseOwnershipChaining = False

theDatabase.Create(False)
 
You manage file growth by using the DataFile objects:

http://msdn2.microsoft.com/de-de/library/ms162176.aspx

Set the Growth property:

http://msdn2.microsoft.com/zh-tw/library/microsoft.sqlserver.management.smo.datafile.growth.aspx

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
message Hi,

Using SMO (VB.NET), I'm creating a new database as shown below. I would
like to change the growth size of the database (to 10%, it's defaulting to
1mb at the moment) and also to set the maximum size I will allow it to grow
to, to 10mb (for testing purposes). How can I do this on the SMO interface?

Thanks for any tips,


Robin




theServer = New Server(Source.Server)

Dim theDatabase As New Database(theServer , newCatalog)

theDatabase.Collation = "SQL_Latin1_General_CP1_CI_AS"
theDatabase.CompatibilityLevel = CompatibilityLevel.Version90
theDatabase.IsFullTextEnabled = False

theDatabase.DatabaseOptions.AnsiNullDefault = False
theDatabase.DatabaseOptions.AnsiNullsEnabled = False
theDatabase.DatabaseOptions.AnsiPaddingEnabled = False
theDatabase.DatabaseOptions.AnsiWarningsEnabled = False
theDatabase.DatabaseOptions.ArithmeticAbortEnabled = False
theDatabase.DatabaseOptions.AutoClose = True
theDatabase.DatabaseOptions.AutoCreateStatistics = True
theDatabase.DatabaseOptions.AutoShrink = True
theDatabase.DatabaseOptions.AutoUpdateStatistics = True
theDatabase.DatabaseOptions.CloseCursorsOnCommitEnabled = False
theDatabase.DatabaseOptions.LocalCursorsDefault = False
theDatabase.DatabaseOptions.ConcatenateNullYieldsNull = False
theDatabase.DatabaseOptions.NumericRoundAbortEnabled = False
theDatabase.DatabaseOptions.QuotedIdentifiersEnabled = False
theDatabase.DatabaseOptions.RecursiveTriggersEnabled = False
theDatabase.DatabaseOptions.BrokerEnabled = True
theDatabase.DatabaseOptions.AutoUpdateStatisticsAsync = False
theDatabase.DatabaseOptions.DateCorrelationOptimization = False
theDatabase.DatabaseOptions.Trustworthy = False
theDatabase.DatabaseOptions.IsParameterizationForced = False
theDatabase.DatabaseOptions.ReadOnly = False
theDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Simple
theDatabase.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple
theDatabase.DatabaseOptions.PageVerify = PageVerify.TornPageDetection
theDatabase.DatabaseOptions.DatabaseOwnershipChaining = False

theDatabase.Create(False)
 
Tom Moreau said:
You manage file growth by using the DataFile objects:

http://msdn2.microsoft.com/de-de/library/ms162176.aspx

Set the Growth property:

http://msdn2.microsoft.com/zh-tw/library/microsoft.sqlserver.management.smo.datafile.growth.aspx
Click to expand...

Ok I see that Tom. I'm going to stick with the defaults and then modify
after database creation. That way I don't have to get my hands dirty adding
filegroups and playing about with stuff I don't understand ;).

Another thing about SQL Express if I may ask, if I specify the filegroup to
grow, say, 10%, will it grow to the 4Gb limit or fail because 10% of 3.9Gb
would take it over the 4Gb limit? Is the limit only for file space actually
committed in tables?

Thanks


Robin
 
I'm not sure what would happen in the case of SQL Express. The limit is not
for space actually used within the file. It is the limit for the file
itself. Thus, you can have a 4GB file and be using only 10MB of it.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

message
Tom Moreau said:
You manage file growth by using the DataFile objects:

http://msdn2.microsoft.com/de-de/library/ms162176.aspx

Set the Growth property:

http://msdn2.microsoft.com/zh-tw/library/microsoft.sqlserver.management.smo.datafile.growth.aspx
Click to expand...

Ok I see that Tom. I'm going to stick with the defaults and then modify
after database creation. That way I don't have to get my hands dirty adding
filegroups and playing about with stuff I don't understand ;).

Another thing about SQL Express if I may ask, if I specify the filegroup to
grow, say, 10%, will it grow to the 4Gb limit or fail because 10% of 3.9Gb
would take it over the 4Gb limit? Is the limit only for file space actually
committed in tables?

Thanks


Robin
 
Back
Top