Amount of data in database

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.