How to get Database Space available
-
Dear All,
How to get space available for the Database ( when we click View-->Task Pad in Database section we can find SPACE AVAILABLE : xxxx Mb.) through sql statement how i can get the that. I can get SIZE of the Database throu Master.dbo.sp_database but how to get the available space.
Thanks
-
sp_spaceused
Displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database.
Syntax
sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
E.g .USE pubs
EXEC sp_spaceused
Hope this helps,Refererence BOL
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Hi Arun,
U can use sp_spaceused SP to get the available space left for the specified database.
Create a job and schedule it so that u can get the information on ur email id.
U can even write a simple stored procedure that will help u in case if space is little u will get an email to increase the space.
HTH
from
Killer
-
Or You could run the undocumented but otherwise well known DBCC SHOWFILESTATS that returns the total number of extents allocated and the number of free extents for the defined datafile(s). From this You can easily calculate the total, used and free space in Kb,Mb,Gb,Tb etc. Run it in the context of the desired database (use northwind go dbcc showfilestats go)
If You also are interested in getting the same for logspace usage, You should look at DBCC SQLPERF(LOGSPACE) that works slightly different since it returns the total size in Mb and the percentage used for the logfile(s) for each database.
Best regards
HPLU, Norway
-
You may want to run sp_updatestats and dbcc updateusage() before you collect and interprete these statistics data !
(Check books online !)
Plan it in downtimes !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Well, I need some help.. I am new to SQL... I mean very very new. I am trying to generate a report using queries, the output of which should be in excel format so that I can conduct statistical analysis using excel or spss. I used the spool command and directed the output to excel but the output is useless because they all seem to be in one cell...Help please
-
Martinson,
There are different ways in which you can have output in Excel.
If you are executing query from Query Analyzer, straight forward option is
Copy the output of Query from QA and Paste to new Excel Sheet.
There are few other options as well to get this goal accomplish.
I will suggest you to go through the article by Yakov Shlafman http://www.sqlservercentral.com/columnists/yShlafman/thebestkeptsecretaboutsqlqueryanalyzer.asp
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com -
Sameer,
Thank you for the help. I can see that it will be very helpful.. Unfortunately, I am not using a query analyzer. I using select statements at the sql prompt that comes with oracle sql * plus and so the GUI is different... Do you know how to do this from Oracle?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply