question

Sagar Bhargava avatar image
Sagar Bhargava asked

SQL Express 2005 4 GB database size limit

Is there a way to RESTORE a 4GB+ size of database to a SQL Express 2005 installation ?

restoresql-server-express
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John Sansom avatar image
John Sansom answered

You cannot restore a database larger than 4GB to SQL Server Express 2005. You will recieve the 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.

See the following Blog post for more details: What Happens When a SQL Server 2005 Express Database goes over 4GB?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

I think I found your answer (although not the one you wanted) here: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!750.entry

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.