October 9, 2007 at 8:45 am
I have 100 GB ++ Database and it keeps growing.
What is the Microsoft best practice to create the db and assign the appropriate size.?
Here is what I have.
CREATE DATABASE Projects
ON
PRIMARY
(NAME = ProjectPrimary,
Filename = 'E:\Projects\sql_data\ProjectPrimary.mdf',
SIZE = (Microsoft best practice db size),
Maxsize = (Microsoft best practice db size),
Filegrowth = (Microsoft best practice db size),
FILEGROUP ProjectsFG
(NAME = ProjectsData1,
Filename = 'F:\Projects\sql_data\ProjectsData1.ndf',
Size = (Microsoft best practice db size),
Maxsize = (Microsoft best practice db size),
Filegrowth = (Microsoft best practice db size),
(NAME = ProjectsData2,
Size = (Microsoft best practice db size),
Maxsize = (Microsoft best practice db size),
Filegrowth = (Microsoft best practice db size),
FILEGROUP ProjectsHistoryFG
(NAME = ProjectHistory1,
Filename = 'F:\Projects\sql_data\ProjectsHistory1.ndf',
Size = (Microsoft best practice db size),
Maxsize = (Microsoft best practice db size),
Filegrowth = (Microsoft best practice db size)
LOG ON
(NAME = Archlog1,
Filename = 'F:\Projects\sql_log\ProjectsLog.ldf',
Size = (25% of data file size),
Maxsize = (Microsoft best practice db size),
Filegrowth = (Microsoft best practice db size)
GO
Thank you for your feedback and suggestions.
Best regards,
TJ 🙂
October 9, 2007 at 10:51 am
I'm not MS, but tend to go for :
SIZE = according to your estimate + grow estimate Mb,
Filegrowth = NOT in percentages !
Avoid autogrowth if you can, so your file is a contigues chunk.
Monitor you database space, so you can plan "manual" growth and your applications don't suffer timeouts due to autogrowth.
Spread in filegroups for performance, keep in mind you can also have multiple files in one filegroup (on #disks), maybe that's ok for you.
With SQL2005 you can recouver at filegroup level, so splitting catalog (primary) from the rest will have advantages if you need a partial restore.
(Check bol for Piecemeal restore )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 9, 2007 at 12:59 pm
Hi SSCrazy,
Thank you for your advise.
TJ
October 9, 2007 at 2:22 pm
Edwin (10/9/2007)
Hi SSCrazy,Thank you for your advise.
TJ
It is ALZDBA 😉
* Noel
October 10, 2007 at 3:03 am
😀
Edwin, I hope it gets you started.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply