What Happens When a SQL Server 2005 Express Database goes over 4GB?

As you may know, SQL Server 2005 Express Edition (which is free), has a database size limit of 4GB. It also has a limit of 1GB of addressable RAM, and it will only use one CPU (although it can be installed on any server). I have had questions in the past about what would happen when a database exceeded the 4GB limit, and I was unsure of the answer.

Madhu Nair (who is very active on the MSDN SQL Server Engine forums) has a good post where he shows exactly what will happen.

 

If you try to restore a database backup that is larger than 4GB to SQL Server 2005 Express Edition, you will get this error:


Msg 1827, Level 16, State 4, Line 1
CREATE DATABASE or ALTER DATABASE failed because the
resulting cumulative database size would exceed your licensed
limit of 4096 MB per database. Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If you have an existing database in SQL Server 2005 Express Edition, and it grows beyond 4GB, you will get this error:

Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database ‘DatabaseName’ because of insufficient disk space in filegroup ‘PRIMARY’. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

So the bottom line is that you should think twice about using SQL Server 2005 Express Edition unless you are pretty confident that you will not hit the 4GB size limit, or else you will have problems. If you do run into this limit, you can try doing an Index Reorganize on all of your indexes, and then try a database file shrink to give yourself some breathing room. Then you need to talk to your bean counters about getting a licensed version of SQL Server 2005 (like Workgroup or Standard Edition).

 

This entry was posted in SQL Server 2005. Bookmark the permalink.

3 Responses to What Happens When a SQL Server 2005 Express Database goes over 4GB?

  1. Glen Villar says:

    Yes, this is true. But I managed to go beyond the limit in SQL Server 2005 Express during a test. I won’t be telling how I did it because that will be breaching the MS’ license agreement.

  2. Nasir says:

    What about if I create two database will both database have 4Gig each

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s