很久不用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
);
