Find database size in query analyzer

Try sp_helpdb

This gives the current size only. I want the Total Size of the DB and/or Space Available. If you click on the Database properties from Enterprise Manager these details are there. I want to find from query analyzer.

And what about sp_helpdb with database name. It shows me total size of all segments and also free space on each of them. But I'm on Sybase and may be, MS SQL will show you something else.

You can get this using T-Sql, I have scripts that will give you DB size, backup size and space left on disc X.

But I cant provide it until I'm back to work on monday.

I will wait for monday Nilsson. Thanks a lot

Here's what EM does (amongst a lot of other things)

select [Total size in KB]=sum(convert(float,size)) * (8192.0/1024.0) from dbo.sysfiles

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

Again you are giving me ways to find Used space. I need the total size!!!

What the heck do you think that first select statement gives you?

SjoerdVerweij : no need to be rude.

son_robin: please elaborate on what you need

*Total size of log and data files for current database
*Total size of log and data files for all databases
*The above divided by Total size left on disc
*Other

Yes.

If you can right click on the database from the enterprise manager (SQL SEerver 2000) and click on properties, it will give size and space available.

SjoerdVerweij, I know your query can return size (current size). But I want to get the space avilable or Total size. Got it??

Thanks SNilsson!

I thought it was a fairly mild response to an inaccurate and mildly accusatory statement. No need to be belligerent.

(Sorry, but I just had the worst lunch in the history of lunches and the indigestion is doing terrible things to my mood).

Okay, what is the difference between current size and total size?

Total size is is the size allocated when you create the database. Current size is the size occupied by the database at present. The space available shows how much size is left so that we can dump data into it.

I need to write a code in the front end to verify if the dtatabse is nearing its available total size and if it does then alert the system admin

Hope this clarification helps.

Thanks,
Robinson.

I see. One more question though: why not set the database to grow automatically?