Update Storage Allocation for a MSSQL Database (Windows Server)

The Problem

On Windows Servers running Microsoft SQL Server, you may encounter an error “Could not allocate space for object” or “Primary filegroup is full” in the course of normal operations:

Unhandled Exception: System.Data.SqlClient.SqlException: Could not allocate space for object 'dbo.SearchedUserItems' ... in database 'db_database' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

...

This error is basically equivalent to a “disk full” error. It means the database does not have any space left to expand, according to its pre-defined size restrictions.

How to Update Storage Allocation for MSSQL Database

First, do a quick check to ensure your Windows Server or VPS has sufficient free disk space on C:\ or your SQL partition. It’s possible that your server or VPS is simply out of space!

If the server has enough disk space, follow these steps to update the Storage Allocation for the database.

1. Open SQL Server Management Studio and connect to your database instance
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

Cick 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.

(Originally posted on: https://dba.stackexchange.com/questions/33700/primary-filegroup-is-full-sql-server-2008/)

This article applies to:

• Windows Server 2016 VPS
• Windows Server 2012 VPS
• Windows Server 2008 VPS