10

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?
Aaron Bertrand
  • 175,645
  • 25
  • 380
  • 589
g00fy
  • 211
  • 1
  • 2
  • 4

3 Answers3

6

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. :(

Community
  • 1
Ali Razeghi
  • 7,315
  • 1
  • 21
  • 36
  • 4
    Oh no! Not the [unreliable, undocumented, unsupported and buggy `sp_msforeachdb`](http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx)!?!? – Aaron Bertrand May 14 '13 at 00:18
  • PS if you're going to use old, backward compatibility views, you can simplify this quite a bit by using `sysaltfiles` (this negates the need to go into each database at all). – Aaron Bertrand May 14 '13 at 00:21
  • 1
    Thanks Aaron for the sysaltfiles, I didn't look into that one yet! sp_msforeachdb has lots of painful issues, but I figured for a SQL Express install where the guy just wants to run a query to see how much free space he has it's fine. – Ali Razeghi May 14 '13 at 00:28
  • Except when it skips databases and doesn't raise an error... – Aaron Bertrand May 14 '13 at 01:16
  • Yea but in this case, do you really think that'll be an issue where the end user just wants a db size count? I guess I shouldn't post it even in cases where the end user will absolutely see if it skipped the DB they are looking for so that it doesn't reinforce bad behavior. I'll just remove that part at this point, you're right, we don't need that command plastered anywhere else. – Ali Razeghi May 14 '13 at 02:17
5

The size of the database is the size of the file. Look at the actual size of the data file (the transaction log doesn't count). Yes indexes count. If you are running out of space consider an upgrade to SQL 2012 Express as that increases the size limit to 10 Gigs.

mrdenny
  • 26,776
  • 2
  • 40
  • 79
1

When sp_spaceused shows the database_size of about 25 GB in your first result set, that includes the data file/indexes, and the transaction log. Only the actual data and indexes count towards the SQL Server Express limits, so you have about 3.3 GB of data (as in your second result set) and the remaining 22 GB will be the transaction log. If you run "sp_helpdb " this shows the real picture, as it reports the data usage and transaction log usage on two separate rows.

Ubercoder
  • 159
  • 6