Space Available on the General properties tab

Question

Post reply

Space Available on the General properties tab

MarvinTheAndriod

SSCarpal Tunnel

Points: 4972
More actions
January 30, 2009 at 5:08 am

#206136

I have a server that is running low on space. When I look at the space available for the database on the General properties tab, it says that there’s about 33 GB. My understanding is that this is space that has been reserved by SQL Server and that this space will be used before SQL starts to eat into the 10 GB space left on the server. Is some of this though reserved for the transaction log? If this is so then can you see how much is reserved for the main file and how much for the transaction log? (The transaction log is located on another drive if this makes any difference)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Use the below mentioned script and see what it says on space uasge for individual file:

sp_msforeachdb 'USE ?

select Name,

(convert(float,size)) * (8192.0/1048576) File_Size,

(convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576) MB_Used,

((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,''SpaceUsed''))) * (8192.0/1048576)) MB_Free

from sysfiles

order by

fileproperty(name,''IsLogFile'')'

MJ

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 2

Or alternatively highlight the DB in question in Management Studio, right-click, "Reports", "Standard Reports", "Disk Usage".

All the relevant info is then displayed nicely in graphs.

I believe you must be running at least SP2 to do this.

extremenovice SSCrazy Points: 2118 More actions · Answer 3

Hi All, i too have found that some of the databases i monitor have no space available in the properties window. I ran the script Manu-J and it provides the results, but is this of great concern. Having checked the disk space there seems to be plenty of it. Also Andy is there a help reference provided to understand all the information in that reports facility/function in management studio?

Thanks for your posts they are always helpful.

🙂

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 4

Hi, glad that was useful.

Not sure that there is any documentation for the reports per se, although someone else may be better at Googling than I.

Here though is a link to the SQL Server 2005 "Performance Dashboard Reports" which are quite handy too.

http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displayLang=en

Install this, restart management studio, then click on an instance (not a database), "Reports" - and you should see "Activity - Top Sessions" and "Performance - Top Queries By Average CPU Time".

Both of these reports are very handy too.