当前位置: > 数据库 > SQL Server 2005 >

检查SQL SERVER 2005数据库文件大小设置与自动增长

时间:2016-06-05 20:31来源:linux.it.net.cn 作者:IT

--判断临时表是否存在
IF OBJECT_ID( 'tempdb..#tmpDBinfo ') IS NULL 
    BEGIN
  PRINT '#tmpDBinfo NOT EXIST'
 END
ELSE
 BEGIN 
  DROP  Table #tmpDBinfo
 END
GO

PRINT 'Creating Table #tmpDBinfo'
GO

--构造临时表
IF OBJECT_ID('tempdb..#tmpDBinfo') IS NULL
BEGIN
 SELECT TOP 0 fileid,groupid,[name],[filename],[size],[maxsize],growth,[status],perf 
 INTO #tmpDBinfo 
 fROM sysfiles 
END

EXECUTE sp_msforeachdb 'INSERT INTO #tmpDBinfo SELECT fileid,groupid,[name],[filename],[size],[maxsize],growth,[status],perf FROM [?].sys.sysfiles '

select  
   name,  
   'groupid'=(case groupid when 0 then N'LogFile' when 1 then N'DataFile' end),
   filename,
   'size'=convert(nvarchar(15), convert (bigint, size) * 8 / 1024) + N' MB',
   'maxsize' = (case maxsize when -1 then N'Unlimited'
     else
     convert(nvarchar(15), convert (bigint, maxsize) * 8) + N' KB' end),
   'growth' = (case status & 0x100000 when 0x100000 then
    convert(nvarchar(15), growth) + N'%'
    else
    convert(nvarchar(15), convert (bigint, growth) * 8 / 1024) + N' MB' end)
from #tmpDBinfo

 

(责任编辑:IT)
------分隔线----------------------------