Sql-server – Database Files and Filegroups

Follow these steps:

1. Identify how much space you want to add to the database storage allocation:
   1. Open windows explorer
   2. Right click on the disk drive that your database files exist on
   3. Select properties
   4. Check how much disk space is available and decide how much of this you want to allocate for the database
      (Suggestion: Leave at least 20% disk space free if you house the database files on the same disk as your OS {Sub-Suggestion: Don't do this! Rebuild/migrate your data to it's own disk; you're screwing yourself on I/O.} and leave at least 8% for a pure data disk; these numbers are estimates of what I think the actual percentage suggestions are.)
2. Update the storage allocation for the database.
   1. Open SSMS
   2. Click the "View" tab
   3. Select "Object Explorer"
   4. Expand the "Databases" folder
   5. Right click the database your trying to bulk insert into
   6. Select "Properties"
   7. Click the "Files" list option from the "Select a page" area at the left of the properties window
   8. Find the "Database files" row with the "Filegroup" as "PRIMARY"
   9. Add whatever number of megabytes you want to add to the database allocation to the "Initial Size (MB)" number
   10. Hit "OK"
       (You might also want to consider your "Autogrowth" values while you're here.)

You want to give your database as much storage allocation as you can afford to give it. If it runs out of space you'll receive this error without auto-grow on and if auto-grow is on you'll take a performance hit each time it has to auto-grow. If you are simply out of disk space then that is your answer and you need a bigger disk.

For me, this is a question of simplicity-of-design vs performance-and-scalability.

If I am designing a non-critical database that won't see a huge amount of traffic, then I typically would go with a single file per database since it is easy to configure and maintain.

However, if your recovery point objective is to be able to recover the database to within several minutes of any failure, and your recovery time objectives are to be able to recover within as short a time as possible, I would carefully plan the filegroups and associated files as:

1. The main filegroup should only contain the system tables. This filegroup can then be very quickly restored or replaced if necessary without affecting business data.
2. A primary filegroup to store business critical data that can be restored quickly enough to meet the recovery time objective and get the business back up-and-running ASAP. I would create as many files for this filegroup as I have separate and distinct drive subsystems on high-speed devices. For instance, if I had 3 separate SSD raid arrays, I might create 3 files to spread the load across the arrays evenly.
3. A secondary filegroup to store historical yet business relevant data that is not critical to get the business up-and-running and yet still needs to be in the database for some reason. Typically this would be on one or more files, depending on performance and cost requirements.

If there is a requirement to store many file-based pieces of data, for instance images or documents, with relationships to data stored in SQL Server, I would use FILESTREAM, which requires its own filegroup.