Skip to content

Create SQL Server Database

    Create SQL Server database with proper configuration taking in 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 [Production2]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'Production2', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Production2.mdf' , --<< this must be in 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'Production2_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Production2_log.ldf' , --<< this must be in 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
    
    sp_helpdb [Production]

    — 1 minute and 10 seconds to create a database for database file size 5 gigs!

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

    –as both the data and log files are on separate physical hard drives, if possible, allocated the data file and log file to the amount of physical drive!

    –we have set the auto growth to increase by 50 mg for the transaction log, but what if a transaction needs more than 50 mg of space, won’t that cause

    –the auto growth to kick in?

    –and if another transaction needs more than 50mg oF space, won’t the transaction log get full and stop working?

    –YES

    –but you must take transaction log backups to truncate the transaction log file so that it does not consume the physical drive!!!