Every Database administrator has to creat databases. It is a very simple task, but you have to know what to do, because it can later affect databases performance . Today, I will show you how to create a database.
There are several ways, using SSMS, using T-SQL or using Powershell.
1) Using Management Studio
a) right click on Databases and choose "New Database"
b) provide the name of Database, "Initial Size" should be 1024KB = 1GB, Enable Autogrowth and "File Growth" set between (512 - 2048) in Megabytes, not in percent. The maximum file size I always set to "Unlimited " or max 2048 GB.
c) Same value set for LOG, and choose the place for data file and log file.
2) Using T-SQL
When you create database via GUI and have everything filled, you can use "Script Action to New Query Window". This is very good if you need to create exactly the same database for another SQL Instance or test environment.
After that, you will see new query windows with the created DB. Ass you can see below:
Here are some details about the Initial Size and AutoGrowth!
Initial Size - settings Initial Size of Database to the ideal size reduces the costs associated with the increase in. When this happens, SQL Server automatically increases the initial size of the database, and locks the database until when the increase is done! It can lead to delays in processing queries and transactions.
AutoGrowth - function Autogrowth can provide that the database is not free space. I prefer to set the File growth in Megabytes, mostly value between 512 - 2048(MB). If the value set in Percent for example 10% for databases with size 100MB it means that every increment will be 10MB, it can cause disk fragmentation.