Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I know that the database can be of certain size X, but some of that file is empty, log data etc

How can I find out how much real data in B/KB/MB is in the database?

share|improve this question
    
Can you please elaborate your question? What Database server are you using? exec sp_helpdb "Database name" using Query –  Umesh Sep 14 '09 at 12:56

4 Answers 4

up vote 8 down vote accepted

Check out sp_spaceused.

sp_spaceused [[ @objname = ] 'objname' ] 
[,[ @updateusage = ] 'updateusage' ]

[ @objname=] 'objname'

Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. Quotation marks are required only if a qualified object name is specified. If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.

If objname is not specified, results are returned for the whole database.

objname is nvarchar(776), with a default of NULL.

[ @updateusage=] 'updateusage'

Indicates DBCC UPDATEUSAGE should be run to update space usage information. When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. Values can be true or false. updateusage is varchar(5), with a default of false.

share|improve this answer
    
Perfect answer, although you could add a link to MSDN. –  Wim ten Brink Sep 14 '09 at 13:39
1  
Happy now, Alex? :-) –  user3914 Sep 14 '09 at 14:06
1  
Am in 7th Heaven now with the link! ;-) –  Wim ten Brink Sep 15 '09 at 11:34

In SQL Server Management Studio you can right click on your database -> Reports -> Disk usage.

share|improve this answer

If I understand correctly, you want to know how much of the database is pure data and how much is there to manage this data. I cannot help but wonder why you'd even want to know this, but okay...

Randolph has the right answer: sp_spaceused I just added the link with additional information. That way, you can check how to use it, since it can also provide information about tables and other objects.

share|improve this answer
1  
Just out of curiosity. We migrate a 8GB database at work to some external consultants. The external consultans gives us a new database containing the migrated data and ONLY the migrated data but suddenly now the database size is 40GB. –  Makach Sep 15 '09 at 8:03

If you're using uncompressed native backups, do a full backup to disk and check its file size - backups don't contain the free space within a database file.

share|improve this answer

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.