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

Question

HI There,

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

Answer 1

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.

Answer 2

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.

Answer 3

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.