Monday, March 12, 2012

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

No comments:

Post a Comment