How to Size Your Database Files?

Guy-Glantser, 2018-01-18 (first published: 2018-01-08)

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:

  1. Enable Instant File Initialization
  2. Create your data files as large as possible, but…
  3. Make sure that you have enough disk space on any potential target server for restore

The post How to Size Your Database Files? appeared first on Madeira Data Solutions.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads