Create A SQL Database

Can create SQL database in two ways either by GUI or script 

Create a database with proper configuration by taking into the following consideration:

• Pre size the data file and auto growth property of the data file
• Pre size the log file and auto growth property of the log file
• Have each data and transaction log file on its own physical drive
• Sizing based on the activity that your environment uses
• Base your size roughly on two or three years of growth patterns

--NOTE: CHANGED THE DEFAULT KB TO MG!

USE master

GO

CREATE DATABASE [DB1]

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'DB1',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1.mdf' , --<< this must be on its own physical drive

SIZE = 5MB , --<< pre size the data file 5000 mb

FILEGROWTH = 1MB )  --<< pre size the log file 100 mb

 LOG ON

( NAME = N'DB1_log',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf' , --<< this must be on its own physical drive

SIZE = 1MB , --<< pre size size of transaction log file 100 mb

FILEGROWTH = 1MB ) --<< pre size size auto growth to increase in increments of 5 mb

GO

  

Size the physical DRIVE size of both the data and the transaction log file based on activity and workload

Make sure to allocate data and log files are on a separate disks to get better performance.

To avoid transaction log file full issue  you must take transaction log backups to truncate the transaction log file so that it does not consume the space on disk!!!