Friday, November 02, 2007

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

2 Comments:

Blogger wilzad said...

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 ?

5:12 PM  
Blogger Madhu K Nair said...

The limitation is for each database. Ie. you can have four 1 GB databases. But any particular databases can not exceed 4 GB

9:50 PM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home