MSSQL 2005 Express Database Size (Observation #1)

Refer to previous posting 'MSSQL 2005 Express More Than 4 GB But Still Running'

We have learned that to check for more accurate database size, you need to make use of the stored procedure called 'sp_spaceused'

Recently, I have made another observation that.

1.0 When the 'reserved' shows a number more than 4GB, you will not be able to create new objects (such as tables).

You will get the error message saying..

"Could not allocate space for object 'dbo.Tr100804' in database 'SVACS' 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."

where ..

  • 'dbo.Tr100804' = table name
  • 'SVACS' = database name

Refer to scenario below.


Illustration #1


Illustration #2

You can see that the 'reserved' size shows 4.19 GB and the backup database file is also quite similar to the size of 4.19 GB.

When this happened, data are still able to be inserted to the database even though new tables are not permitted to be created.

This suggests that..

2.0 When the 'data' size shows a number more than 4 GB, you really can't insert any more data to the database.

Refer to illustration #1

3.0 Conclusion for now:

Therefore, this means that the 4 GB data limit actually is quite tricky and it applies to different scenarios.

Refer to illustration #1
  • 'Reserved' > 4GB = Cannot create new objects (i.e tables), but can still insert data.
  • 'Data' > 4GB = Cannot insert new data
Based on this observation, I like to suggests that the 4 GB data limit is not a joke or a gimmick to encourage you to go for standard edition which will cost about RM 7,000.

Comments