Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
You can check the backup_size column of msdb.dbo.backupset. –  Shawn Melton Feb 22 at 16:32
add comment

3 Answers

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.

share|improve this answer
2  
SQL Server 2008 R2 Express also has a 10 GB limit (just for completeness) –  marc_s May 14 '13 at 12:19
add comment

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

share|improve this answer
4  
    
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 0:21
    
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 0:28
    
Except when it skips databases and doesn't raise an error... –  Aaron Bertrand May 14 '13 at 1: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 2:17
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.