Writing an article about how to create a database might seem a bit too simple to many but I was surprised when talking to people that they didn’t know why I would do it this way. When you create a database it creates a copy of the model database. The model database is the template used when you create your database so if you want to adopt a standard for all newly created databases then you make these changes in model. One thing to be aware of that I’ve seen is that many applications will specify the size in their create database statement so if you, say, set the create size to be larger than the size that they specify in their create statement an error will occur. So I don’t recommend changing the minimum size. Also, you should be pre-creating your databases manually every time too. I do however set the growth increments away from the defaults of 1MB and 10%. Change these to something more appropriate and NEVER use a percentage growth increment for your databases. In fact you shouldn’t autogrow your databases – only use this as a backstop.
Onto the create database statement – see below…
CREATE DATABASE MySampleDatabase ON PRIMARY (NAME = MySampleDatabase_sys , FILENAME = 'D:\MSSQL\Data\MySampleDatabase_sys.mdf' , SIZE = 100 , FILEGROWTH = 100 , MAXSIZE = UNLIMITED) , FILEGROUP DATA DEFAULT (NAME = MySampleDatabase_data , FILENAME = 'D:\MSSQL\Data\MySampleDatabase_data.ndf' , SIZE = 1000 , FILEGROWTH = 1000 , MAXSIZE = UNLIMITED) LOG ON (NAME = MySampleDatabase_log , FILENAME = 'E:\MSSQL\Data\MySampleDatabase_sys.ldf' , SIZE = 200 , FILEGROWTH = 200 , MAXSIZE = UNLIMITED)
The reason for the 3 files as minimum (2 is the default) is that you are now keeping a separate filegroup for your system files. The PRIMARY filegroup is compulsory for all databases so lets keep the system files in here and create a new filegroup (called DATA in this case) which is the default filegroup. Be aware that despite you creating a new DEFAULT filegroup, which is used when no filegroup is specified, if someone scripts objects from another database and uses this script against this database and it specifies the PRIMARY filegroup then objects will be inserted here. One way to stop this from happening is use Policy Based Management. More on that in a separate post.
Note that if the PRIMARY filegroup is available then you have more options available to you in a corruption scenario. The boot page of a database (Page 9) resides in the primary filegroup – if corruption occurs to the PRIMARY filegroup you may have no option but to restore from backup. My point here is that if the PRIMARY filegroup is available then you can run DBCC checks against the database. Therefore if you have a PRIMARY filegroup that is protected for normal day to day operations then you might have less chance of getting a unrecoverable database.
The sizes that I have used are for this example here only but I would use 100MB for the _sys files in the PRIMARY filegroup. That is plenty of space. Also, if your PRIMARY filegroup becomes full, you won’t be able to grow your database.