3

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?

3

This restriction of 'instant file initialization' (aka. SetFileValidData) is documented:

Note The file cannot be a network file, or be compressed, sparse, or transacted.

  • Thank you very much. Exactly the kind of information I was after. Appreciate your willingness to point me in the right direction. – Ryan Peterson Jan 14 '16 at 21:30
2

Its a blunder to restore a BIG database on SQL Server with no instant file initialization enabled. The amount of time taken to restore with no IFI is MUCH larger than amount of time taken to restore with IFI enabled.

When IFI is enabled what it does is this permission keeps SQL Server from "zeroing out" new space when you create or expand a data file. This time is saved and thus making your restore faster.

You can use below query to check how much your restore operation is completed

select percent_completed from sys.dm_exec_requests where session_id=xxx---restore session id

If you can try, start restoring with NO IFI and you would see percent_completed would be 0 for quite few amount of time. while this would immediately start showing some value. So bottom line IFI is must.

  • The column name on mine was percent_complete instead of percentage_completed. If you don't know the session_id off the top of your head, you can also get all backup percentages: select DB_NAME(database_id) name,percent_complete from sys.dm_exec_requests where command like 'BACKUP%' – Brain2000 Jan 15 '18 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.