Let’s say you need to create a new SQL Server database, which is going to grow very fast, but you don’t know how fast. What would be the initial size of the data file?
On one hand, you want to make it as large as possible in order to avoid future auto-growth operations and disk fragmentation. So if you have a 5TB disk just for the data file, why not allocate a data file of 5TB and consume the entire disk space from the beginning?
The problem with this approach is that it would take a long time to create this file. But since this is a one-time offline operation, it’s not such a big problem. The real problem is when you need to restore the database. SQL Server will attempt to allocate 5TB for the data file during restore. This is a real problem, because you usually perform a restore when there is a crisis and the application is down.
Thankfully, you can enable Instant File Initialization (IFI). This feature allows SQL Server to allocate the file instantly, without having to zero out all the bits. So allocating 5MB or 5TB will take the same time – instantly.
But there is still a gotcha. If your production server with the 5TB data disk fails, and now you need to restore the database to a different server, the new server might not have a disk with 5TB of free space. In this case, the restore will fail, because it won’t be able to allocate a 5TB file on disk. And you will not be able to restore the database. Oops…
So the bottom line is:
Enable Instant File Initialization
Create your data files as large as possible, but…
Make sure that you have enough disk space on any potential target server for restore