Sql-server – How to check if I am hitting the Express Edition size limit

limitssql serversql-server-2005sql-server-express

I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused:

what is the actual size of the database

  1. How can I check if my DB is hitting the size limit?
  2. Is unallocated space the space left untill I hit the limit?
  3. How much space do I have left?
  4. Does index_size count in the limit?

Best Answer

Here is a good script I shamelessly ripped from here:

use [Insert DB Name]

select
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name, 'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name, 'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a

This is good because it will give you the free space in each DB file (you might have multiple files and perhaps someone set it up to put some objects in each) as well as the free size.

For example, you have have a 4GB data file which has 3GB of free space. Maybe you have 1 MDF without a lot of data but a NDF with lots of data. This query will tell you the free size in each file and what DB that file is allocated to. Remember to add up all the 'SPACE_USED_MB' for each DB to get the total size.

Good luck!

Edit: Removed a unsupported and buggy command that I thought I could get away with posting in here for this query. :(

Related Question