SQL Server – Adding Data File to Existing Database Taking Too Long

sql serversql server 2014sql-server-2012

I am trying to add file using below

USE [master]
GO
ALTER DATABASE [foo] ADD FILE ( NAME = N'foo_1', FILENAME = N'D:\Data10\Data\foo10.ndf' , SIZE = 102400000KB , FILEGROWTH = 0) TO FILEGROUP [PRIMARY]
GO

Actually i have to add 2 TB file to this 3 TB disk, but trying to add in chunks of 100 GB

But it seems to be running for past 30 mins

HOw can i improve this?

Update:

The Query above completed in 40 mins. The wait was ASYNC_IO_COMPLETION

Also, this database is 24*7 and has DB mirroring configured.

Now, i am trying to increase in 50 GB chunks using below

USE [master]
GO
ALTER DATABASE [foo] MODIFY FILE ( NAME = N'foo_10', SIZE = 153600000KB )
GO

Above seems to be running for past 10 mins with wait (183043ms)PREEMPTIVE_OS_WRITEFILEGATHER

Best Answer

I have a feeling something is iffy about your setup here.

If you run the following script:

DBCC TRACEON(3004, 3605, -1);
GO

CREATE DATABASE Crap;
GO

EXEC sys.sp_readerrorlog;
GO

DROP DATABASE Crap;
GO

You should see this in the error log:

NUTS

In other words, only the transaction log needs to be zeroed out. The data file should skip this.

I can run this commands and have it finish instantly as well:

ALTER DATABASE Crap
    ADD FILE ( NAME = N'Crap_1', FILENAME = N'F:\SQL\Data\Crap_1.ndf', SIZE = 102400000KB, FILEGROWTH = 0 )
    TO FILEGROUP [PRIMARY];
GO

If you're on a new enough version of SQL Server (as your question >implies), you can see if this query helps:

SELECT servicename, instant_file_initialization_enabled
FROM   sys.dm_server_services;

If that doesn't work, you'll have to go back in time and use whoami to determine that.

Keep in mind that your server needs to be restarted to enable IFI, so if you just added the priv, or you added it and never restarted SQL, it won't be in effect.

Also, this database is 24*7 and has DB mirroring configured

If this is sync mirroring, perhaps IFI isn't enabled on the parter? Have you checked to see what's up over there?

Related Question