监视SQL SERVER data file size 的脚本

由于做管理员的人都需要监视一些东西。比如cpu mem disk 之类的东西。同样，如果是能够方便的监控数据库的大小并且将他记录下来的话。就能够很方便的规划你的数据库的容量。
功能:将bat文件方到计划任务中.在规定的时间内运行后回产生个以当天日期为文件名的脚本.
如果是要在统计分析的时候可以将log 导入到excel 中制作成图表.

monitor.bat :
@ECHO off
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM Monitor MS SQL Server Data File size on all database
REM to log file
REM Author : Hao Wang hao2.wang@samsung.com
REM Samsung SDS China
REM Version 0.1
REM 2005/3/14
REM These scripts can then be run in batch. Use the AT
REM scheduler to schedule the monitor job.
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ECHO .
SET M_log_sql=c:monitor.sql
SET M_log_TARGET=c:mssqllog
if not exist %M_log_TARGET% ul mkdir %M_log_TARGET%
osql.exe -E -i%M_log_sql% -o%M_log_TARGET%/%date:~4,4%%date:~9,2%%date:~12,2%.log -w 500 -n

monitor.sql:
/*

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Monitor MS SQL Server Data File size on all database
to log file
Author : Hao Wang hao2.wang@samsung.com
Version 0.1
2005/3/14
Samsung SDS China
These scripts can then be run in batch. Use the AT
scheduler to schedule the monitor job.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

*/

set nocount on
set quoted_identifier on
use master
DECLARE @DatabaseName varchar(500),@Database_result varchar(500)
DECLARE authors_cursor CURSOR FOR
select name from master.dbo.sysdatabases
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
into @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
select @Database_result= 'use '+@DatabaseName+';
select '''+@DatabaseName+''' as DatabaseName, substring(name,1,20) as " DataName " ,substring(filename,1,70) as " DataFile Name ",size "DataSize"from sysfiles'
exec (@Database_result)
use master
FETCH NEXT FROM authors_cursor into @DatabaseName
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

上一篇：SQL Server 2000+ MS WIN2003群集服务配置

下一篇：关于SQL Server中几个未公布的访问注册表的扩展存储过程

昵称 (必填)

验证码 (必填)