获取数据库大小 数据库大小查询

CREATE TABLE #table(IP NVARCHAR(100),name NVARCHAR(100),PhysicalName NVARCHAR(100),typeDesc NVARCHAR(50),GB DECIMAL(18,6))

DECLARE @Str NVARCHAR(1000)

DECLARE @IP TABLE(ID INT IDENTITY(1,1),IP NVARCHAR(100),CityName NVARCHAR(200))

INSERT INTO @IP(CityName,IP)

--SELECT 'db1','127.0.0.1' UNION ALL

--SELECT 'db2','127.0.0.2' UNION ALL

DECLARE @Min INT,@Max INT,@IP_Address NVARCHAR(100)

SET @Min=(SELECT MIN(ID) FROM @IP)

SET @Max=(SELECT MAX(ID) FROM @IP)

TRUNCATE TABLE tbb_EmailJobs

WHILE(@Min<=@Max)

BEGIN

SET @IP_Address=(SELECT IP FROM @IP WHERE ID=@Min)

SET @IP_Address='['+@IP_Address+']'

SET @Str='SELECT *

FROM (SELECT '+@IP_Address+',A.name,B.name AS PhysicalName, B.type_desc,B.size*8/1024.00/1024 AS ''GB''

FROM '+@IP_Address+'.master.sys.databases A

JOIN '+@IP_Address+'.master.sys.master_files B ON A.database_id=B.database_id

)aa WHERE GB>10'

INSERT INTO #table (IP,name,PhysicalName,typeDesc,GB)

EXEC(@Str)

SET @Min=@Min+1

END

合并一行 合并一条

create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2,'ccc')

go

ALTER function dbo.f_str(@id int)

returns varchar(100)

as

begin

declare @str varchar(1000)

set @str='' select @str=@str+','+cast(value as varchar)

from tb where id = @id

set @str=right(@str , len(@str) - 1)

return @str

end

go

调用函数

select id , value = dbo.f_str(id) from tb group by id

获取数据库大小

DECLARE @IP NVARCHAR(100),@Lnk NVARCHAR(200),@Str NVARCHAR(1000)

SET @IP='127.0.0.1'

DECLARE @DA TABLE(ID INT IDENTITY(1,1),NAME NVARCHAR(100),Data DECIMAL(10,2),LOG DECIMAL(10,2))

DECLARE @Data TABLE(ID INT IDENTITY(1,1),NAME NVARCHAR(100),Data DECIMAL(10,2),LOG DECIMAL(10,2))

SET @Lnk='SQLOLEDB'+''''+','+''''+'Data Source='+@IP+';DBN=master;UID=Monitor;PWD=………………'

SET @Str='SELECT A.name,'

+'CASE WHEN B.type_desc=''ROWS'' THEN B.size*8/1024.00/1024.00 END AS Data,'

+'CASE WHEN B.type_desc=''LOG'' THEN B.size*8/1024.00/1024.00 END AS lOG '

+'FROM OPENDATASOURCE('+''''+@Lnk+''''+').master.sys.databases A '

+'JOIN OPENDATASOURCE('+''''+@Lnk+''''+').master.sys.master_files B ON A.database_id=B.database_id'

INSERT INTO @DA (NAME,Data,LOG)

EXEC(@Str)

INSERT INTO @Data (NAME,Data,LOG)

SELECT Name,SUM(ISNULL(Data,0)) AS DATA,SUM(ISNULL(LOG,0)) AS LOG

FROM @DA

GROUP BY NAME

--SELECT SUM(Data),SUM(log)

--FROM @Data

--WHERE NAME NOT IN('master','model','msdb','ReportServer','ReportServerTempDB','distribution','tempdb')

SELECT Name,CASE WHEN log<>0 THEN '数据大小：'+CONVERT(NVARCHAR(10),Data)+'GB'+';日志大小：'+CONVERT(NVARCHAR(10),LOG)+'GB'

ELSE '数据大小：'+CONVERT(NVARCHAR(10),Data)+'GB' END AS DataDesc

FROM @Data

WHERE NAME NOT IN('master','model','msdb','ReportServer','ReportServerTempDB','distribution',

'tempdb','AdventureWorks','AdventureWorksDW')

ORDER BY NAME

查看数据库文件使用的磁盘空间使用情况

WITH T1 AS (

SELECT DISTINCT

REPLACE(vs.volume_mount_point,':\','') AS Drive_Name ,

CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB ,

CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Free_Space_GB

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs

)

SELECT

Drive_Name,

Total_Space_GB,

Total_Space_GB-Free_Space_GB AS Used_Space_GB,

Free_Space_GB,

CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent

FROM T1

查询数据库大小及其所剩磁盘容量大小

WITH T1 AS ( SELECT DISTINCT REPLACE(vs.volume_mount_point,‘:’,‘’) AS Drive_Name , CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB , CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Free_Space_GB FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs )

SELECT * FROM (SELECT a.Drive_Name,B.name,b.PhysicalName,b.type_desc,B.GB, Total_Space_GB,Total_Space_GB-Free_Space_GB AS Used_Space_GB,Free_Space_GB, CAST(Free_Space_GB100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent FROM T1 a JOIN(SELECT SUBSTRING(B.physical_name,1,1) AS Drive_Name,A.name,B.name AS PhysicalName, B.type_desc,B.size8/1024.00/1024 AS ‘GB’ FROM master.sys.databases A JOIN master.sys.master_files B ON A.database_id=B.database_id) b ON a.Drive_Name=B.Drive_Name )A ORDER BY A.Free_Space_GB ASC,A.GB DESC

SELECT A.Drive_Name,A.name AS DBName,A.PhysicalName,A.type_desc, A.GB,Total_Space_GB,Used_Space_GB,Free_Space_GB,Free_Space_Percent FROM (SELECT a.Drive_Name,B.name,b.PhysicalName,b.type_desc,B.GB, Total_Space_GB,Total_Space_GB-Free_Space_GB AS Used_Space_GB,Free_Space_GB, CAST(Free_Space_GB100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent FROM (SELECT DISTINCT REPLACE(vs.volume_mount_point,‘:’,‘’) AS Drive_Name , CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB , CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Free_Space_GB FROM sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs) a JOIN(SELECT SUBSTRING(B.physical_name,1,1) AS Drive_Name,A.name,B.name AS PhysicalName, B.type_desc,B.size8/1024.00/1024 AS ‘GB’ FROM master.sys.databases A JOIN master.sys.master_files B ON A.database_id=B.database_id) b ON a.Drive_Name=B.Drive_Name )A ORDER BY A.Free_Space_GB DESC,A.GB DESC

–循环计算数据库大小 USE DB_F_CentralBackUP GO

CREATE TABLE #Result(ID INT IDENTITY(1,1) PRIMARY KEY,IP NVARCHAR(100),Name NVARCHAR(100),

Data DECIMAL(10,2),LOG DECIMAL(10,2),DB DECIMAL(10,2))

--获取数据库大小

DECLARE @IP NVARCHAR(100),@Lnk NVARCHAR(200),@Str NVARCHAR(1000),@min INT,@max INT

--SET @IP='127.0.0.1'

DECLARE @table TABLE (ID INT IDENTITY(1,1),IP NVARCHAR(100))

DECLARE @DA TABLE(ID INT IDENTITY(1,1),NAME NVARCHAR(100),Data DECIMAL(10,2),LOG DECIMAL(10,2))

DECLARE @Data TABLE(ID INT IDENTITY(1,1),NAME NVARCHAR(100),Data DECIMAL(10,2),LOG DECIMAL(10,2))

INSERT INTO @table (IP)

SELECT DISTINCT IP FROM Central.DB

SET @min=(SELECT MIN(ID) FROM @table)

SET @max=(SELECT MAX(ID) FROM @table)

WHILE(@min<=@max)

BEGIN

SET @IP=(SELECT IP FROM @table WHERE ID=@min)

DELETE FROM @DA

DELETE FROM @Data

SET @Lnk='SQLOLEDB'+''''+','+''''+'Data Source='+@IP+';DBN=master;UID=Monitor;PWD=………………'

SET @Str='SELECT A.name,'

+'CASE WHEN B.type_desc=''ROWS'' THEN B.size*8/1024.00/1024.00 END AS Data,'

+'CASE WHEN B.type_desc=''LOG'' THEN B.size*8/1024.00/1024.00 END AS lOG '

+'FROM OPENDATASOURCE('+''''+@Lnk+''''+').master.sys.databases A '

+'JOIN OPENDATASOURCE('+''''+@Lnk+''''+').master.sys.master_files B ON A.database_id=B.database_id'

INSERT INTO @DA (NAME,Data,LOG)

EXEC(@Str)

INSERT INTO @Data (NAME,Data,LOG)

SELECT Name,SUM(ISNULL(Data,0)) AS DATA,SUM(ISNULL(LOG,0)) AS LOG

FROM @DA

GROUP BY NAME

INSERT INTO #Result (IP,Name,Data,LOG,DB)

SELECT @IP,Name,Data,LOG,Data+LOG AS 数据库大小--,

--CASE WHEN log<>0 THEN '数据大小：'+CONVERT(NVARCHAR(10),Data)+'GB'+';日志大小：'+CONVERT(NVARCHAR(10),LOG)+'GB'

-- ELSE '数据大小：'+CONVERT(NVARCHAR(10),Data)+'GB' END AS DataDesc

FROM @Data

WHERE NAME NOT IN('master','model','msdb','ReportServer','ReportServerTempDB','distribution',

'tempdb','AdventureWorks','AdventureWorksDW')

ORDER BY NAME

SET @min=@min+1

END

推荐文章

评论可见，请评论后查看内容，谢谢！！！评论后请刷新页面。