Monitoring Database disk space

An article Data files: To Autogrow Or Not To Autogrow? recommends NOT to rely on auto-grow, because it causing delays in unplanned times.

We should mtonitor database files(both data and log), and if they close to max capacity, manually increase the size.
 However it doesn't give references, how to monitor the free space inside databases.
 
I've tried to look how to do it. 

It can be done manually using   
execute sp_spaceused for the database in question or 
sp_SOS (can be downloaded from
http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure)

Alternatively you can run SQL commands as suggested in Http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359 by Michael Valentine Jones

select 
[FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) 
from dbo.sysfiles a

More useful article Monitor database file sizes with SQL Server Jobs describes how to setup monitoring


 Finally I found the excellent article

Managing Database Data Usage With Custom Space Alerts, that can be followed even support personnel without much DBA experience.

posted @ Sunday, September 30, 2012 11:44 AM
Print

Comments on this entry:

No comments posted yet.

Your comment:



(not displayed)

 
 
 
 
 

Live Comment Preview:

 
«October»
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678