CODEWITHSUNDEEP
sql-server
DEE
DEE

Reputation: 373

Specifying MAXSiZE for MDF LDF files while creating Database in SQL server 2005

while we create DB , we specify the maxsize of LDF ,MDF 

alter database myDB
add file 
(name = mydata, -- alias
Filename = 'c:\mydata.ndf', -- physical location
Size = 20MB, -- starting size; data/mo
Maxsize = 2064)

i know the size would be subjective to the deployment . i just want to know , is there any guidelines on how to arrive at this number OR can i use UNLIMITED always ,if i use UNLIMITED ,are there any pitfalls

any comments/suggestions

Thanks Deepak

Upvotes: 2

Views: 2975

Answers (3)

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11367

I think it's perfectly okay not to specify a max size. But I would consider partitioning the database and store the files on differnt drives if the database grows over, say, 50 - 60 GB.

Upvotes: 1

gbn
gbn

Reputation: 432672

Unlimited could theoretically fill the disk up. However, setting a maxsize less than disk capacity would mean simply the database stops working before the disk fills up.

In practice, it makes no difference. GROWTH and initial size is more important because it can affect physical file fragmentation. I've never set MAXSIZE, personally.

Upvotes: 3

Raj More
Raj More

Reputation: 48058

Usually, databases that are used for production purposes are the ones where you do NOT want to specify max size for a number of reasons - primarily because when that size is reached, production stops!

There was a time when I could go for size limiting somebody / some process, but now, space is cheap.

So far, in umpteen years of professional work, I have never specified a MaxSize. But then, I have never had a good business case to limit the size either.

Go with the unlimited option.

Upvotes: 1

Related Questions