Creating a new database with limits using SMO

[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.CloseCursorsOnCommitEn abled = False
theDatabase.DatabaseOptions.LocalCursorsDefault = False
theDatabase.DatabaseOptions.ConcatenateNullYieldsN ull = False
theDatabase.DatabaseOptions.NumericRoundAbortEnabl ed = False
theDatabase.DatabaseOptions.QuotedIdentifiersEnabl ed = False
theDatabase.DatabaseOptions.RecursiveTriggersEnabl ed = False
theDatabase.DatabaseOptions.BrokerEnabled = True
theDatabase.DatabaseOptions.AutoUpdateStatisticsAs ync = False
theDatabase.DatabaseOptions.DateCorrelationOptimiz ation = False
theDatabase.DatabaseOptions.Trustworthy = False
theDatabase.DatabaseOptions.IsParameterizationForc ed = False
theDatabase.DatabaseOptions.ReadOnly = False
theDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Simple
theDatabase.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple
theDatabase.DatabaseOptions.PageVerify = PageVerify.TornPageDetection
theDatabase.DatabaseOptions.DatabaseOwnershipChain ing = False

theDatabase.Create(False)

[Tom Moreau]

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/lib...le.growth.aspx

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Robinson" <to******************@myinboxtoomuchtoooften.comwr ote in
message news:eh*******************@news.demon.co.uk...
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.CloseCursorsOnCommitEn abled = False
theDatabase.DatabaseOptions.LocalCursorsDefault = False
theDatabase.DatabaseOptions.ConcatenateNullYieldsN ull = False
theDatabase.DatabaseOptions.NumericRoundAbortEnabl ed = False
theDatabase.DatabaseOptions.QuotedIdentifiersEnabl ed = False
theDatabase.DatabaseOptions.RecursiveTriggersEnabl ed = False
theDatabase.DatabaseOptions.BrokerEnabled = True
theDatabase.DatabaseOptions.AutoUpdateStatisticsAs ync = False
theDatabase.DatabaseOptions.DateCorrelationOptimiz ation = False
theDatabase.DatabaseOptions.Trustworthy = False
theDatabase.DatabaseOptions.IsParameterizationForc ed = False
theDatabase.DatabaseOptions.ReadOnly = False
theDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Simple
theDatabase.DatabaseOptions.UserAccess = DatabaseUserAccess.Multiple
theDatabase.DatabaseOptions.PageVerify = PageVerify.TornPageDetection
theDatabase.DatabaseOptions.DatabaseOwnershipChain ing = False

theDatabase.Create(False)

[Robinson]

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:eT****************@TK2MSFTNGP03.phx.gbl...

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/lib...le.growth.aspx

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

[Tom Moreau]

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

"Robinson" <to******************@myinboxtoomuchtoooften.comwr ote in
message news:eh*******************@news.demon.co.uk...

"Tom Moreau" <to*@dont.spam.me.cips.cawrote in message
news:eT****************@TK2MSFTNGP03.phx.gbl...

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/lib...le.growth.aspx

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