SQLserver 監控資料檔案

很久不用Sqlserver了

今天居然連查詢分析器還是打電話求助找到的。。。。

汗啊。。。。

google+自己瞎倒騰，寫了個sp監控dbfile，本來想再做的智慧點用bcp 直接匯出，不過出於安全原因，生產資料庫把master..xp_cmdshell給禁用了，所以還是手動上去查log表了

程式碼如下：

-----------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

-- =============================================

-- StoredProcedure: master.dbo.sp_dbfilemonitor

-- =============================================

ALTER PROCEDURE dbo.sp_dbfilemonitor

as

declare @dbName varchar(50)

declare @command varchar(4000)

declare @current_date varchar(16)

declare @DriveFreeGB varchar(50)

create table #tb_DiskSpace

([Drive] varchar(2),

[MBfree] int);

insert #tb_DiskSpace exec master.dbo.xp_fixeddrives;

select @DriveFreeGB=convert (nvarchar(50),cast(convert (bigint,MBfree)/1024.0 as decimal(9,2)))+N' GB' from #tb_DiskSpace where drive='D';

drop table #tb_DiskSpace;

declare dbName_cursor CURSOR FOR select [name] from master.dbo.sysdatabases order by [name]

open dbName_cursor

FETCH NEXT FROM dbName_cursor into @dbName

while @@FETCH_STATUS = 0

begin --

set @command =

'insert into master.dbo.DB_monitor

select host_name(),

convert(char(16),getdate(),120),'''

+@dbname+

''',sf.name FileLogicalName,

sf.filename FilePath,

case sf.maxsize when -1 then N'''+'Unlimited'+'''

else convert (nvarchar(15),convert (bigint,maxsize)*8)+N'''+' KB'+''' end MaxSize_KB,

case sf.maxsize when -1 then N'''+'Unlimited'+'''

else convert (nvarchar(15),cast(convert (bigint,maxsize)*8/1024.0 as decimal(9,2)))+N'''+' MB'+''' end MaxSize_MB,

convert (nvarchar(15),cast(convert (bigint,size)*8/1024.0 as decimal(9,2)))+N'''+' MB'+''' UsedSize_MB,

case sf.maxsize when -1 then N'''+'Unlimited'+'''

else convert (nvarchar(15),cast(convert (bigint,maxsize-size)*8/1024.0 as decimal(9,2)))+N'''+' KB'+''' end FreeSize_MB,

case status & 0x100000 when 0x100000 then convert(nvarchar(3),growth)+N'''+'%'

+''' else convert(nvarchar(15),growth*8)+N'''+' KB'+'''end Growth,

case status & 0x40 when 0x40 then '''+'log only'

+''' else '''+'data only'+''' end Usage,+N'''+@DriveFreeGB+'''

from '+@dbName+'.dbo.sysfiles sf';

exec(@command);

--select @command;

--insert into tt select @command;

FETCH NEXT FROM dbName_cursor into @dbName;

end

CLOSE dbName_cursor;

deallocate dbName_cursor;

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

--------------------------------------

create table dbo.DB_monitor(
HostName varchar(50) not null,
LogDate varchar(16) not null,
DatabaseName varchar(50) not null,
FileLogicalName varchar(50) not null,
FilePatch varchar(2000) not null,
MaxSizeKB varchar(150) not null,
MaxSizeMB varchar(100) not null,
UsedSizeMB varchar(100) not null,
FreeSizeMB varchar(100) not null,
Growth varchar(100) not null,
Usage varchar(10) not null,
DriverFreeSizeGB varchar(50)notnull
);

[@more@]