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]
GOCREATE PROC [dbo].[usp_GetDatabaseSpaceUsed]
ASdeclare @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]
ENDIF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'DatabaseSpaceUsed')
BEGIN
TRUNCATE TABLE DatabaseSpaceUsed;
ENDINSERT 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]
GOCREATE PROC usp_GetDatabaseSpaceUsed
ASdeclare @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]
ENDIF EXISTS (SELECT [name] FROM sys.objects WHERE [name] = 'DatabaseSpaceUsed')
BEGIN
TRUNCATE TABLE DatabaseSpaceUsed;
ENDINSERT 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=0SET 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.DatabaseSpaceUsedselect @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
endDrop 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 @commandfetch next from tmpJob into @id, @serverip, @linkservername
END
close tmpJob
deallocate tmpJobSET NOCOUNT OFF;
後續將做報表,可以得出任意時間段的資料庫增量報表!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9932141/viewspace-445544/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Jtti:sql server怎麼增量備份資料庫JttiSQLServer資料庫
- Blazor使用sql server 資料庫BlazorSQLServer資料庫
- SQL Server資料庫安全SQLServer資料庫
- SQL Server 資料庫映象SQLServer資料庫
- SQL Server 資料庫索引SQLServer資料庫索引
- 資料庫映象 (SQL Server)資料庫SQLServer
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- Sql Server資料庫資料匯入到SQLite資料庫中Server資料庫SQLite
- Sql Server 資料庫學習-常用資料庫 物件SQLServer資料庫物件
- SQL Server資料庫恢復,SQL Server資料恢復,SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- SQL Server收縮資料庫SQLServer資料庫
- 管理SQL Server資料庫安全SQLServer資料庫
- SQL Server資料庫遷移SQLServer資料庫
- 還原SQL Server資料庫SQLServer資料庫
- sql server跟蹤資料庫SQLServer資料庫
- SQL Server資料庫監控SQLServer資料庫
- 資料庫例項 (SQL Server)資料庫SQLServer
- Sql Server系列:資料庫操作SQLServer資料庫
- Sql Server系列:資料庫物件SQLServer資料庫物件
- 備份SQL Server資料庫SQLServer資料庫
- 修改SQL Server資料庫地址SQLServer資料庫
- SQL Server 移動資料庫SQLServer資料庫
- SQL Server資料庫備份SQLServer資料庫
- SQL Server 資料庫同步配置SQLServer資料庫
- SQL Server資料庫巡檢SQLServer資料庫
- sql server 資料庫收縮SQLServer資料庫
- 刪除sql server資料庫中所有資料SQLServer資料庫
- 資料倉儲—資料庫—SQL Server 介紹資料庫SQLServer
- 【資料庫學習】資料庫平臺:mysql,sql server資料庫MySqlServer
- 在SQL Server資料庫中匯入MySQL資料庫Server資料庫MySql
- ORACLE資料庫裡表匯入SQL Server資料庫Oracle資料庫SQLServer
- 【SQL】Oracle資料庫資料量及效能資訊收集SQLOracle資料庫
- SQL SERVER備份資料庫檔案(使用SSMS)SQLServer資料庫SSM
- 在SQL Server下資料庫連結的使用SQLServer資料庫
- Sql Server系列:資料庫組成及系統資料庫SQLServer資料庫
- [Kogel.Subscribe.Mssql]SQL Server增量訂閱,資料庫變更監聽SQLServer資料庫
- MySQL 資料庫增量資料恢復案例MySql資料庫資料恢復
- SQL Server 跨資料庫查詢SQLServer資料庫