SQL SERVER收集資料庫使用增量資料

管理的SQL Server的資料庫達100多個，最近想了解每個資料庫的增量情況。

由於資料庫分佈在多臺伺服器中，首先需要收集資料庫的空間使用情況，在前面做過磁碟空間的分析和收集，正好利用原有的一些Linked Server。原來有一張pubLinkServer表，對就的是伺服器地址與Linked Server名。

pubLInkServer表：

CREATE TABLE [dbo].[pubLinkServer](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [LinkServerName] [varchar](50) ,
 [LinkServerIp] [varchar](20) ,
  [Status] [varchar](10) ,
 )

1、客戶端儲過程

----SQL Server 2000----

USE [master]
GO

CREATE PROC [dbo].[usp_GetDatabaseSpaceUsed]
AS

declare @command nvarchar(4000);
DECLARE @Error_num int;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#sysfiles') is NOT NULL
BEGIN
 DROP TABLE #sysfiles
END;
CREATE TABLE #sysfiles (
     [DBNAME] [VARCHAR](50)
 ,[fileid] [int]
 ,[groupid] [int]
 ,[size] [int]
 ,[maxsize] [int]
 ,[growth] [int]
 ,[status] [int]
 ,[name] [nvarchar](128)
 ,[filename] [nvarchar](260)
 ,[UserAccess] [nvarchar](60)
 ,[Updateability] [nvarchar](60)
 ,[RecoveryMode] [nvarchar](60)
 ,[spaceused] [int]
)

--improve the SpaceUsed error (Name)
INSERT INTO #sysfiles
EXEC( 'sp_msforeachdb  ''use [?]; Select ''''?'''' DBName,fileid,groupid,size,maxsize,growth,status,[name],filename, CONVERT(sysname,DatabasePropertyEx(''''?'''',''''UserAccess'''')) AS UserAccess, CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Updateability'''')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''''?'''',''''Recovery'''')) AS RecoveryMode,CAST(FILEPROPERTY(sysfiles.name, ''''SpaceUsed '''' ) AS int) AS spaceused from sysfiles ''');

 

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[Master].[dbo].[DatabaseSpaceUsed]'))
BEGIN
CREATE TABLE [dbo].[DatabaseSpaceUsed](
  [ServerName] [varchar](128)
 ,[DatabaseName] [sysname]
 ,[LogicalFileName] [varchar](128)
 ,[PhysicalFile] [varchar](255)
 ,[FileType] [varchar](20)
 ,[FileSize] [varchar](23)
 ,[SPACEFREE] [decimal](15, 2)
 ,[FreeSpacePct] [varchar](11)
 ,[MaxFileSize] [varchar](15)
 ,[SpaceRemainingMB] [varchar](10)
 ,[Growth] [int] NULL
 ,[GrowthType] [varchar](10)
 ,[PollDate] [datetime] NOT NULL
 ,[status] [varchar](30)
    ,[user_access] [nvarchar](60)
 ,[ReadOnly] [varchar](10)
    ,[recovery_model] [nvarchar](60)
) ON [PRIMARY]
END

IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'DatabaseSpaceUsed')
BEGIN
    TRUNCATE TABLE DatabaseSpaceUsed;
END

INSERT INTO dbo.DatabaseSpaceUsed
EXEC sp_executesql @command=N'
 SELECT  @@servername as ServerName,

   DBname AS DatabaseName,

   CAST([Name] AS varchar(128)) AS NameofFile,

   CAST(filename AS varchar(255)) AS PhysicalFile,

   CASE WHEN groupid<>0 THEN ''ROWS''
    ELSE ''LOG''
    END AS FileType,

   CASE 
    WHEN (SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' GB''
    ELSE CAST(CAST((SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' MB''
   END AS FileSize,

   CAST((SIZE * 8 / 1024.0) - (SPACEUSED / 128.0) AS DECIMAL(15,2)) as SPACEFREE,

   cast(cast(100 * cast((SIZE * 8/1024.0) - (SPACEUSED/128.0) as int)/cast(SIZE * 8 / 1024.0 as DECIMAL(6,1)) as DECIMAL(6,2)) as varchar(10))+''%'' AS FreeSpacePct,

   MaxFileSize = CASE WHEN maxsize = -1 OR maxsize = 268435456 THEN ''UNLIMITED''
    WHEN maxsize = 0 THEN ''NO_GROWTH''
    WHEN maxsize <> -1 OR maxsize <> 0 THEN CAST(((maxsize * 8) / 1024) AS varchar(15))
    ELSE ''Unknown''
    END,

   SpaceRemainingMB = CASE WHEN maxsize = -1 OR maxsize = 268435456 THEN ''UNLIMITED''
   WHEN maxsize <> -1 OR maxsize = 268435456 THEN CAST((((maxsize - size) * 8) / 1024) AS varchar(10))
    ELSE ''Unknown''
    END,

   Growth = CASE WHEN growth = 0 THEN 0
    WHEN growth > 0 then case when status >= 1048576 then growth else ((growth * 8)/1024) end
    END,

   GrowthType = CASE WHEN status >= 1048576 THEN ''PERCENTAGE''
   WHEN status > 0 THEN ''MBs''
    ELSE ''Unknown''
    END,

   getdate() as PollDate,

            CONVERT(sysname,DATABASEPROPERTYEX(DBname,''Status'')) as status,
            UserAccess,
            case when Updateability=''READ_ONLY'' then ''Yes''
    else ''No''
    end,
   RecoveryMode
 FROM #sysfiles
 WHERE   DBname not in (''tempdb'',''model'',''ReportServer'',''ReportServerTempDB'')
   ORDER BY fileid
 '

SET NOCOUNT OFF;

 

----SQL Server 2005-----

USE [master]
GO

CREATE PROC usp_GetDatabaseSpaceUsed
AS

declare @command nvarchar(4000);

SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#TmpSpaceUsed') is NOT NULL
BEGIN
 DROP TABLE #TmpSpaceUsed
END;

CREATE TABLE #TmpSpaceUsed (
  DBNAME    VARCHAR(128),
  FILENME   VARCHAR(128),
  FILESIZE int,
  SPACEUSED FLOAT) ;

 

INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use [?]; Select ''''?'''' DBName, Name FileNme, SIZE,fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''');

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseSpaceUsed]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DatabaseSpaceUsed](
  [ServerName] [varchar](128)
 ,[DatabaseName] [sysname]
 ,[LogicalFileName] [varchar](128)
 ,[PhysicalFile] [varchar](255)
 ,[FileType] [varchar](20)
 ,[FileSize] [varchar](23)
 ,[SPACEFREE] [decimal](15, 2) NULL
 ,[FreeSpacePct] [varchar](11)
 ,[MaxFileSize] [varchar](15)
 ,[SpaceRemainingMB] [varchar](10)
 ,[Growth] [int] NULL
 ,[GrowthType] [varchar](10)
 ,[PollDate] [datetime] NOT NULL
 ,[status] [varchar](30)
    ,[user_access] [nvarchar](60)
 ,[ReadOnly] [varchar](10)
    ,[recovery_model] [nvarchar](60)
) ON [PRIMARY]
END

IF EXISTS (SELECT [name] FROM sys.objects WHERE [name] = 'DatabaseSpaceUsed')
BEGIN
    TRUNCATE TABLE DatabaseSpaceUsed;
END

INSERT INTO dbo.DatabaseSpaceUsed
EXEC sp_executesql @command=N'
 SELECT  @@servername as ServerName,

   B.name AS DatabaseName,

   CAST(A.[Name] AS varchar(128)) AS NameofFile,

   CAST(A.physical_name AS varchar(255)) AS PhysicalFile,

   type_desc AS FileType,

   --((size * 8)/1024) AS FileSize,
   CASE 
    WHEN (SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' GB''
    ELSE CAST(CAST((SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' MB''
   END AS FileSize,

   CAST((A.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) as SPACEFREE,

   --CAST(100 * (CAST (((D.FILESIZE/128.0 -CAST(FILEPROPERTY(D.FILENME, 
   -- ''SpaceUsed'' ) AS int)/128.0)/(D.FILESIZE/128.0)) 
   --AS decimal(4,2))) AS varchar(8)) +  ''%''  AS FreeSpacePct,
   cast(cast(100 * cast((A.SIZE * 8/1024.0) - (D.SPACEUSED/128.0) as int)/cast(a.SIZE * 8 / 1024.0 as DECIMAL(6,1)) as DECIMAL(6,2)) as varchar(10))+''%'' AS FreeSpacePct,

   MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN ''UNLIMITED''
    WHEN max_size = 0 THEN ''NO_GROWTH''
    WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
    ELSE ''Unknown''
    END,

   SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN ''UNLIMITED''
   WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
    ELSE ''Unknown''
    END,

   Growth = CASE WHEN growth = 0 THEN 0
    WHEN growth > 0 then case when is_percent_growth = 1 then growth else ((growth * 8)/1024) end
    END,

   GrowthType = CASE WHEN is_percent_growth = 1 THEN ''PERCENTAGE''
   WHEN is_percent_growth = 0 THEN ''MBs''
    ELSE ''Unknown''
    END,

   getdate() as PollDate,

   A.state_desc as status,
            B.user_access_desc,
   case when B.is_read_only=1 then ''Yes'' else ''No'' end,
   b.recovery_model_desc
 FROM master.sys.master_files A
  left join master.sys.databases B
  on a.database_id=b.database_id
  left JOIN #tmpSpaceUsed D
  ON A.Name = D.FILENME and b.name=d.DBname
 WHERE A.type_desc in (''ROWS'',''LOG'') and B.name not in (''tempdb'',''model'',''ReportServer'',''ReportServerTempDB'')
 --AND state = 0
   ORDER BY A.database_id, A.file_id
 '

SET NOCOUNT OFF;

2、服務端

--資訊表--

CREATE TABLE [dbo].[pubDatabaseInfo](
  [Id] [int] IDENTITY(1,1) NOT NULL
 ,[LinkServerId] [int] NULL
 ,[ServerIp] [varchar](20) NULL
 ,[ServerName] [varchar](128) NULL
 ,[DatabaseName] [nvarchar](128) NULL
 ,[LogicalFileName] [varchar](128) NULL
 ,[PhysicalFile] [varchar](255) NULL
 ,[FileType] [varchar](60) NULL
 ,[MaxFileSize] [varchar](15) NULL
 ,[SpaceRemainingMB] [varchar](10) NULL
 ,[CollectDate] [datetime] NULL
 ,[Flag] [varchar](10) NULL
 ,[User_Access] [nvarchar](128) NULL
 ,[ReadOnly] [varchar](20) NULL
 ,[Recovery_Model] [nvarchar](128) NULL
) ON [PRIMARY]

--資料表---

CREATE TABLE [dbo].[pubDatabaseSpace](
  [RecordNum] [bigint] IDENTITY(1,1) NOT NULL
 ,[DatabaseFileId] [int] NULL
 ,[FileType] [varchar](50) NULL
 ,[FileSize] [varchar](23) NULL
 ,[SpaceFree] [decimal](15, 2) NULL
 ,[FreeSpacePct] [varchar](12) NULL
 ,[Growth] [int] NULL
 ,[GrowthType] [varchar](10) NULL
 ,[Date] [datetime] NULL
 ,[Status] [varchar](50) NULL
) ON [PRIMARY]

--資訊表收集--

create proc usp_GetDatabaseInfo
AS
declare @command nvarchar(4000)

--get database information
declare @id int,@serverip varchar(20),@linkservername varchar(20)
declare tmpJob cursor for
  SELECT id, LinkServerIp ,LinkServerName FROM dbo.pubLinkServer WHERE Status='ENABLE' and id not in (2,5,11,14)
open tmpJob
fetch next from tmpJob into @id, @serverip, @linkservername
while (@@fetch_status=0)
begin
  set @command=N'insert into dbo.pubDatabaseInfo
 (
  [LinkServerId]
 ,[ServerIp]
 ,[ServerName]
 ,[DatabaseName]
 ,[LogicalFileName]
 ,[PhysicalFile]
 ,[FileType]
 ,[MaxFileSize]
 ,[SpaceRemainingMB]
 ,[CollectDate]
 ,[Flag]
 ,[User_Access]
 ,[ReadOnly]
 ,[Recovery_Model]
 )
 SELECT '+ CONVERT(varchar(10),@id) +',' +''''+ @serverip +''''+', ServerName,DatabaseName,LogicalFileName,PhysicalFile,FileType,MaxFileSize,SpaceRemainingMB,getdate(),''Y'', user_access, ReadOnly, recovery_model FROM ' + @linkservername+'.master.dbo.DatabaseSpaceUsed'
    exec sp_executesql @command
 fetch next from tmpJob into @id, @serverip, @linkservername
END
close tmpJob
deallocate tmpJob

--資料收集--

create proc [dbo].[usp_GetDatabaseData]
@debug bit = 0
AS
declare @command nvarchar(4000)
declare @para nvarchar(100)
declare @cnt int
declare @i int
set @i=0

SET NOCOUNT ON;

--get database information
declare @id int,@serverip varchar(20),@linkservername varchar(20)
declare tmpJob cursor for
  SELECT id, LinkServerIp ,LinkServerName FROM dbo.pubLinkServer WHERE Status='ENABLE' and id not in (2,5,11,14)
open tmpJob
fetch next from tmpJob into @id, @serverip, @linkservername
while (@@fetch_status=0)
begin
----while a database deleted from a server, it will do to chenge the flag of collect database space
select @command=N'
    declare @cnt int
    set nocount on
 select ServerName,DatabaseName,LogicalFileName into #tmpDel from dbo.pubDatabaseInfo where flag=''Y'' and linkserverid='+cast(@id as char(5)) + '
 except
 select ServerName,DatabaseName,LogicalFileName from '+@linkservername+'.master.dbo.DatabaseSpaceUsed

 select @cnt =count(DatabaseName) from #tmpDel
 
 if @cnt>=1
 begin
 Update dbo.pubDatabaseInfo
  set Flag=''N''
  from dbo.pubDatabaseInfo A
  join #tmpDel B
  on A.ServerName=B.ServerName and A.DatabaseName=B.DatabaseName and A.LogicalFileName=B.LogicalFileName
  where A.linkserverid='+cast(@id as char(5))+ ' and A.ServerName=B.ServerName and A.DatabaseName=B.DatabaseName and A.LogicalFileName=B.LogicalFileName
 end

 Drop table #tmpDel   
    '

 IF @debug = 1 PRINT @command
 else
  exec sp_executesql @command

-- while a database add to a server , its information will add to dbo.pubDatabaseInfo table
set @command = N'
    declare @cnt int
    set nocount on
 select ServerName,DatabaseName,LogicalFileName into #tmpAdd from '+@linkservername+'.master.dbo.DatabaseSpaceUsed
 except
 select ServerName,DatabaseName,LogicalFileName from dbo.pubDatabaseInfo where flag=''Y'' and LinkServerId='+cast(@id as char(5)) + '
 select @cnt = count(*) from #tmpAdd
    if @cnt>=1
  begin
  insert into dbo.pubDatabaseInfo
   (
     [LinkServerId]
    ,[ServerIp]
    ,[ServerName]
    ,[DatabaseName]
    ,[LogicalFileName]
    ,[PhysicalFile]
    ,[FileType]
    ,[MaxFileSize]
    ,[SpaceRemainingMB]
    ,[CollectDate]
    ,[Flag]
    ,[User_Access]
    ,[ReadOnly]
    ,[Recovery_Model]
   )
    SELECT '+ CONVERT(varchar(10),@id) +',' +''''+ @serverip +''''+', A.ServerName,A.DatabaseName,A.LogicalFileName,A.PhysicalFile,A.FileType,A.MaxFileSize,A.SpaceRemainingMB,getdate(),''Y'', a.user_access, a.ReadOnly, a.recovery_model FROM ' + @linkservername+'.master.dbo.DatabaseSpaceUsed A join #tmpAdd B on A.ServerName=B.ServerName and A.DatabaseName=B.DatabaseName and A.LogicalFileName=B.LogicalFileName where A.ServerName=B.ServerName and A.DatabaseName=B.DatabaseName and A.LogicalFileName=B.LogicalFileName
 end
 Drop table #tmpAdd
    '

 IF @debug = 1 PRINT @command
 else
  exec sp_executesql @command

--collect the Database space used daily
 set @command=N'
  insert into dbo.pubDatabaseSpace
  (
    [DatabaseFileId]
   ,[FileType]
   ,[FileSize]
   ,[SpaceFree]
   ,[FreeSpacePct]
   ,[Growth]
   ,[GrowthType]
   ,[Date]
   ,[Status]
  )
  select B.id,A.FileType,A.FileSize,A.SPACEFREE,A.FreeSpacePct,A.Growth,A.GrowthType,A.PollDate,A.status
  FROM ' + @linkservername+'.master.dbo.DatabaseSpaceUsed as A
  left join dbo.pubDatabaseInfo B
  on B.Servername=A.Servername and a.DatabaseName=b.DatabaseName and a.LogicalFileName=b.LogicalFileName
 '
 IF @debug = 1 PRINT @command
 exec sp_executesql @command

  fetch next from tmpJob into @id, @serverip, @linkservername
 END
close tmpJob
deallocate tmpJob

SET NOCOUNT OFF;

後續將做報表，可以得出任意時間段的資料庫增量報表！

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-445544/，如需轉載，請註明出處，否則將追究法律責任。