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:
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?