We have a large Read Only SQL DB (SQL 2012) that we’re restoring to a NAS as a current duct tape solution. I had wondered why the restore process seemed to be hanging.
Part of my guidance along the way has come from:
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/
and
http://www.brentozar.com/blitz/instant-file-initialization/
I believe I’ve identified that Instant File Initialization may not available when creating or restoring to a UNC path (or may need additional setup). SQL code below demonstrates that
DBCC TRACEON(3004,3605,-1)
GO
CREATE DATABASE [DestinedForGreatness]
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
CREATE DATABASE [DestinedForGreatness]
ON PRIMARY
( NAME = N’DestinedForGreatness’, FILENAME = N’\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness.mdf’ )
LOG ON
( NAME = N’DestinedForGreatness_log’, FILENAME = N’\\LittleBlackBox\SQL2012data\MSSQL\Data\DestinedForGreatness_log.ldf’)
GO
DROP DATABASE DestinedForGreatness
GO
EXEC sp_readerrorlog
GO
DBCC TRACEOFF(3004,3605,-1)
I can see in the first instance when creating a DB locally it doesn't 0 fill the MDF from the errorlog. In the second instance it does.
I’d be interested to know if there’s any workaround for this to take advantage of IFI. Would application of the Windows security settings on the server I'm restoring to resolve this?
Best Answer
This restriction of 'instant file initialization' (aka.
SetFileValidData) is documented: