Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I check for the real size of a database 1

Status
Not open for further replies.

babeo

Technical User
Mar 30, 2000
398
CA
Hi
Can sp_configure or sp_databases will tell me the real size of a database or just a predefine space set for the database? Could someone tell me how to check the real size of a database?
Thanks
 
How about sp_spaceused. When used without specifying a table name, it shows info on the entire database...
 
you can:

as noted use 'sp_spaceused'

use sp_helpdb

use 'dbcc showfilestats'

use EM and do it through the gui

you should note that 'sp_spaceused' is not always exact. it is based on values in system tables and is not always guaranteed exact. you can ensure accuracy with

use dbname
go
sp_spaceused @updateusage='true'

note if you use 'updateusage' on a large db, it might run long. Paul
 
Hi CosmoKramer

I ran your suggested command, and try to make some calculation, but I still don't get it, could get figure out for me please. Tx. I compare the results from 2 commands:

>sp_spaceused
database_name database_size reserved data index_size unused
------------- ------------- ----------- ------------ ----------- --------
logs 35943.0 KB 27964460 KB 22113128 KB 5827448 KB 23884 KB

>sp_databases
database_name database_size remarks
------------- ------------- -----------
logs 36806144 NULL

Thanks
 
babeo,

database_size = amount of space requested in the create and alter commands
reserved = amount of space currently reserved for use
data = amount of space being used by data
index_size = amount of space being used by indexes
unused = amount of reserved space not currently in use

Are you sure of your display? Here is one of mine:

Code:
 database_name                  database_size
 ------------------------------ -------------
 hns_live                       20100.0 MB
 reserved        data            index_size      unused
 --------------- --------------- --------------- -----------
 15197046 KB     9804764 KB      5369786 KB      22496 KB
Notice the MB instead of your KB under database_size.....
 
Hi CosmoKramer

Sorry for mistyping, yeah, you are right, it is MB, but here is my point:
(take your database for example)
- What is the actual total size of the database ? Is it the numbers of columns "data" + "index_size" + "unused"?
Thanks
 
Yes - that's also the "reserved" total. The other values just break it down by user tables, indexes, and free space.

HTH
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top