SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Size Your Database Files?

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.

Guy Glantser

Guy Glantser, Data Platform MVP, is the leader of the Israeli PASS chapter and also the CEO and founder of Madeira Data Solutions. His career has been focused on the Microsoft Data Platform for the past 20 years, performing various database roles as either an on-site DBA, an external consultant or a speaker. Guy is involved in many activities in the Microsoft Data Platform community. He occasionally speaks at community events, such as PASS Summit, SQLBits, SQL Saturdays and user groups around the world. He also co-hosts the SQL Server Radio podcast.

Comments

Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...