SQL Server Express 4GB Size Limitation and the Error
I have seen lot of queries regarding the size limitation in SQL Server Express. We all know there is limitaiton and its 4GB. But is it exactly 4GB or when the engine stops you pumping more data into the database? And whats the error you gets? These are the few FAQ on this subject. Then I thought I should try myself and document it.
SQL Server Version
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(a) When We try to restore a database of size more than 4 GB
RESTORE FILELISTONLY FROM DISK='D:\GP.bak'
RESTORE DATABASE GPtogpx
FROM DISK='D:\GP.bak'
WITH MOVE'Gp' TO 'd:\GP.mdf',
MOVE 'Gp_log' TO 'd:\GP_log.ldf'
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.
(b) When a database crosses 4 GB Limit
sp_spaceused Result
database_name database_size unallocated space
-------------------- ------------------------------------
ABC 4216.75 MB 77.16 MB
reserved data index_size unused
------------------ ------------------ ------------------
4115296 KB 4098792 KB 11824 KB 4680 KB
Error
Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database 'GPtogpx' 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 of this study is, the limitation is just not in the paper and it really going to hit you sooner or later if the database going to grow beyond 4GB. So go for Licensed Edition
SQL Server Version
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(a) When We try to restore a database of size more than 4 GB
RESTORE FILELISTONLY FROM DISK='D:\GP.bak'
RESTORE DATABASE GPtogpx
FROM DISK='D:\GP.bak'
WITH MOVE'Gp' TO 'd:\GP.mdf',
MOVE 'Gp_log' TO 'd:\GP_log.ldf'
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.
(b) When a database crosses 4 GB Limit
sp_spaceused Result
database_name database_size unallocated space
-------------------- ------------------------------------
ABC 4216.75 MB 77.16 MB
reserved data index_size unused
------------------ ------------------ ------------------
4115296 KB 4098792 KB 11824 KB 4680 KB
Error
Msg 1101, Level 17, State 12, Line 3
Could not allocate a new page for database 'GPtogpx' 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 of this study is, the limitation is just not in the paper and it really going to hit you sooner or later if the database going to grow beyond 4GB. So go for Licensed Edition
posted by Madhu K Nair at
2:10 AM
2 Comments:
we know for sure that the limit is there, even some limit was there at the times of MSDE, after seing your blog one doubt araised. does the space limit is for the entire databases or for individual, ie what if i have 4 databases with 1GB each, will it show error if we try to enter more records to any of the databases ?
The limitation is for each database. Ie. you can have four 1 GB databases. But any particular databases can not exceed 4 GB
Post a Comment
Subscribe to Post Comments [Atom]
<< Home