Check database size and free space

atapojha · #1 (**permalink**) 09-01-08, 19:15

Hi Guys

A novice question.

How do I check the database size and how much free space is available to the database through sql query?

sp_helpdb does not give me the free space available.

Thanks

Atap

pdreyer · #2 (**permalink**) 09-03-08, 04:04

What version of ASE?
Works fine on all versions I checked from ASE 11.9 to 15

Code:

1> sp_helpdb master
2> go
 name                     db_size       owner                    dbid   created        status                                                                                                 
 ------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------ 
 master                         33.0 MB sa                            1 Jan 01, 1900   no options set                                                                                         

(1 row affected)
 device_fragments               size          usage                free kbytes 
 ------------------------------ ------------- -------------------- ----------- 
 master                         3.0 MB        data and log                   0 
 master                         30.0 MB       data and log               30368

sqlserverdba1 · #3 (**permalink**) 11-26-11, 12:07

select a.file_id,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'S paceUsed')/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.physical_name,100)from sys.database_files a

pdreyer · #4 (**permalink**) 11-28-11, 07:41

sqlserverdba1,

Besides the fact that this thread is more than 3 years old the query you posted is not for Sybase. You working in wrong forum?

Internet Services

Web Development

Digital Marketing

Consumer Tech