asked
database creation query taking a long time
Hi guys, Hope some of you SQL expert can help me out here. So i am running a Query that will create a database that has a initial size of 150gig and max size of 380gig The Query been running for about 10mins now and its still not done, on the drive i see its allready alloacted the Space, so 150 gig less Space left. Its still saying "Executing query" and not giving any errors. I have tried lowering the inital size of databse to a few gig and all and that Query work withing 1-2 min. Is it normal for it to take this long, or am i just being unpatient, i am running SQL server 2012 64bit enterprise on a pretty fast computer With lots of ram. Any help would be highly appritiated Here is the Query i am running : USE [master] GO /****** Object: Database [patstatoct2013] Script Date: 07/05/2013 12:00:00 ******/ CREATE DATABASE [patstatoct2013] ON PRIMARY ( NAME = N'patstatoct2013_dat', FILENAME = N'D:\pstatdb\patstatoct2013dat.mdf' , SIZE = 150000MB , MAXSIZE = 400000MB , FILEGROWTH = 51200KB ) LOG ON ( NAME = N'patstatoct2013_log', FILENAME = N'D:\pstatdb\patstatoct2013log.ldf' , SIZE = 50000MB , MAXSIZE = 100000MB , FILEGROWTH = 15360KB ) COLLATE SQL_Latin1_General_CP1_CI_AS GO EXEC dbo.sp_dbcmptlevel @dbname=N'patstatoct2013', @new_cmptlevel=90 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [patstatoct2013].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [patstatoct2013] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [patstatoct2013] SET ANSI_NULLS OFF GO ALTER DATABASE [patstatoct2013] SET ANSI_PADDING OFF GO ALTER DATABASE [patstatoct2013] SET ANSI_WARNINGS OFF GO ALTER DATABASE [patstatoct2013] SET ARITHABORT OFF GO ALTER DATABASE [patstatoct2013] SET AUTO_CLOSE OFF GO ALTER DATABASE [patstatoct2013] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [patstatoct2013] SET AUTO_SHRINK OFF GO ALTER DATABASE [patstatoct2013] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [patstatoct2013] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [patstatoct2013] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [patstatoct2013] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [patstatoct2013] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [patstatoct2013] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [patstatoct2013] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [patstatoct2013] SET DISABLE_BROKER GO ALTER DATABASE [patstatoct2013] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [patstatoct2013] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [patstatoct2013] SET TRUSTWORTHY OFF GO ALTER DATABASE [patstatoct2013] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [patstatoct2013] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [patstatoct2013] SET READ_WRITE GO ALTER DATABASE [patstatoct2013] SET RECOVERY FULL GO ALTER DATABASE [patstatoct2013] SET MULTI_USER GO ALTER DATABASE [patstatoct2013] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [patstatoct2013] SET DB_CHAINING OFF
Do you have the "[perform volume maintenance][1]" set for the SQL Server service account? If not, then enabling it will improve the initialization of the data files when creating/expanding them. Another thing to consider is the contention/performance of the disk sub system. [1]:
http://technet.microsoft.com/en-us/library/ms175935(v=sql.105).aspx
if IFI is on then the only saving you might get would be to create the database with a smaller log file - IFI doesnt affect logfile growth events. Having said that, if you know the logfile needs to be 50GB then 'it needs to be 50GB' and you might as well create it that size as create it smaller and then have to grow it. On the up side, you only create the database once so the pain isnt going to keep coming back
Yeah, pretty sure it's just the time to allocate that much space all at once on the disk.
16 People are following this question.
