Clever Geek Handbook

Defining MAXSiZE for LDF MDF Files When Creating a Database on SQL Server 2005

while we create the database, we specify the maxsize 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 will be subjective for deployment. i just want to know if there are any recommendations on how to arrive at this number or i can use UNLIMITED always, if i use UNLIMITED are there any pitfalls

any comments / suggestions

Thanks Deepak

+2


source to share


3 answers


Unlimited can theoretically fill the disk. However, setting maxsize less than the disk capacity means that the database will stop working before the disk is full.



In practice, it doesn't matter. GROWTH and initial size are more important because it can affect the fragmentation of the physical file. I have never installed MAXSIZE personally.

+3


source


Typically the databases that are used for production purposes are the ones that you do NOT want to specify a maximum 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 someone / some process, but now space is cheap.



Until now, in my many years of professional work, I have never specified MaxSize. But then I never had a good business case to limit the size.

Go with unlimited option.

+1


source


I think it's perfectly okay not to specify a maximum size. But I would consider splitting the database and storing the files on different disks if the database is going to grow, say 50-60 GB.

+1


source






More articles:


All Articles