I try to create database by generating script of another database, which was a huge Database. When I run the script below
USE [master]
GO
CREATE DATABASE [YourDB] ON PRIMARY
( NAME = N'YourDB_1', FILENAME = N'G:DataYourDB.mdf', SIZE = 230683968KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [DWINDEX]
( NAME = N'YourDB_1_index', FILENAME = N'G:DataYourDB_1.ndf', SIZE = 86941440KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'YourDB_1_log', FILENAME = N'F:LogsSQLYourDB_2.ldf', SIZE = 2568128KB , MAXSIZE = 2048GB , FILEGROWTH = 10%),
( NAME = N'YourDB_2_log', FILENAME = N'F:LogsSQLYourDB_3.ldf', SIZE = 32256KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB ),
( NAME = N'YourDB_3_log', FILENAME = N'F:LogsSQLYourDB_4.ldf', SIZE = 19520KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB ),
( NAME = N'YourDB_31_log', FILENAME = N'F:LogsSQLYourDB_5.ldf', SIZE = 79488KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
It runs for almost half hour and return following error.
Error
Msg 5149, Level 16, State 3, Line 3
MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical
file 'G:DataYourDB_1.ndf'.
Msg 1802, Level 16, State 4, Line 3
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Solution:
When we create script of big Database, it goes to claim space at hard disk given in Size property of File and query took lot of time. Solution of the above query is quite simple, always set size property of mdf file to 4000KB and LDF file property to 1024KB to create new Database.