SQLServer统计采集数据库相关信息

 

在MS Sql Server中可以能过以下的方法查询出磁盘空间的使用情况及各数据库数据文件及日志文件的大小及使用利用率:

1、查询各个磁盘分区的剩余空间：
Exec master.dbo.xp_fixeddrives

2、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）
select * from [数据库名].[dbo].[sysfiles]
转换文件大小单位为MB：
select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

3、查询当前数据库的磁盘使用情况：
Exec sp_spaceused

４、查询数据库服务器各数据库日志文件的大小及利用率
DBCC SQLPERF(LOGSPACE)

5、查询数据库服务器名，实例名，版本号

SELECT SERVERPROPERTY('machinename'),@@SERVERNAME,SERVERPROPERTY ('edition'),@@version

 

 

--用SQL统计SQLServer表存储空间大小, 遍历库中每个表的记录数及空间占用情况。

CREATE TABLE #tmp (
name varchar(50),	--表名
rows int,	--记录数
reserved varchar(50),	--保留空间
data varchar(50),	--使用空间
index_size varchar(50),	--索引使用空间
unused varchar(50));	--未用空间

INSERT INTO #tmp (name,rows,reserved,data,index_size,unused) EXEC sp_msforeachTable "sp_spaceused '?'"

SELECT t.name,t.[rows],t.reserved,t.data,t.index_size,t.unused 
FROM (	SELECT *,CAST(replace(data,' KB','') as INT) as sort from #tmp) t 
ORDER BY t.sort DESC
GO
DROP TABLE #tmp

　　

 

参考文章： https://www.cnblogs.com/fygh/archive/2012/02/13/2348709.html?spm=a2c4e.10696291.0.0.fd7519a4FdNCoJ 

 

 方法一：使用 sp_spaceused

declare @dbname varchar(100) 
declare db_cur cursor for
select name from sys.sysdatabases where dbid>4 and name<>'distribution'
open db_cur
fetch next from db_cur  into @dbname
while @@FETCH_STATUS=0
 begin
  print('use '+QUOTENAME(@dbname))
  Print('go')
  Print('sp_spaceused')
  fetch next from db_cur  into @dbname
 end
close db_cur
deallocate db_cur

 

 

方法二：sys.database_files 和sp_MSforeachdb相结合

--统计某个实例中部分数据库大小：
use master
go
create table dbsize (dbname varchar(100),size int)

--将数据库名和大小插入数据库表中
exec sp_MSforeachdb "insert into master.dbo.dbsize 
select '?' as dbname,sum(size) as size from ?.sys.database_files"

说明：我们利用sp_MSforeachdb循环数据库，再在每个数据库中查询sys.database_files 视图来统计数据文件的大小，得到的结果放入到一个表中，

然后再使用这个表来做统计；

select * from master.dbo.dbsize

--计算筛选数据库总大小
select SUM(size)*8/1024 as dbsize from dbsize where dbname not in
('master','tempdb','model','msdb','distribution')

 

 

 

方法三：sys.sysaltfiles与sys.databases结合统计

--db file size
select db.name,sf.name,sf.filename,sf.size from sys.sysaltfiles sf inner join sys.databases db 
on sf.dbid=db.database_id 
where db.database_id>4 and db.name <>'distribution'

--dbsize
select SUM(size)*8/1024 from sys.sysaltfiles sf inner join sys.databases db 
on sf.dbid=db.database_id 
where db.database_id>4 and db.name <>'distribution'

 

 

经实践，方法二和方法三还是有点轻微区别的： 方法二是统计的是当前激活的数据库，方法三是统计所有的数据库(包括脱机，离线状态下的数据库) 

 

 

人人都是DBA系列： https://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_sp_info_collection.html