前沿

作为MSSQL DBA来讲，相信大家在平时的工作中也有很多总结，在此和大家分享一些有用的脚本。也希望大家多多的补充。

背景

作为MSSQL DBA来说，需要做的一个很重要的工作就是数据库的监控和监控处理。其中，一类比较重要的监控就是Disk Monitor。监控磁盘空间的使用状况，当达到某一个设定的阀值时，会发出监控邮件，这个时候需要DBA及时处理，以免数据文件或者日志文件的增长导致了磁盘空间用尽，从而导致磁盘写爆或者宕机的可能。

这个时候，一系列重要的问题就摆在了DBA的面前：

空间紧张的Driver上有哪些数据库？
这些数据库的数据文件和日志文件有多大？
数据文件或者日志文件到底有多少空间可供收缩？
整个数据库实例的文件大小情况怎样？

如何快速而准确的获取到这些信息是我们本文的目的。

在此，希望和大家对工作总结的分享，希望对大家有用

脚本

在上面的背景下，话不多说，直接上脚本，也可以直接下载附件。

<!--database_Statistics_V2.sql<!--database_Statistics_V2.sql database_Statistics_V2.sql (4.34 KB, 下载次数: 201)

USE master
GO
SET NOCOUNT ON
--===================public variables
declare
@db_name sysname
,@drive sysname
;
select
@db_name = NULL --NULL: all databases
,@drive = 'D' --NULL:all drives
;
--================end public variables
--===================private variables
declare
@sql nvarchar(max)
,@exec_sql nvarchar(max)
,@database_name sysname
,@reservedpages int
;
select
@sql = N'
select @reservedpages = sum(a.total_pages)
from sys.partitions p WITH(NOLOCK)
inner join sys.allocation_units a WITH(NOLOCK)
on p.partition_id = a.container_id'
,@database_name = N''
,@exec_sql = N''
,@reservedpages = 0
;
--================end private variables
--================for reserved pages saving
declare
@tb_database_reservedpages table
(
id int identity(1,1) not null PRIMARY key
,database_id int null
,database_name sysname null
,reservedpages int null
);
DECLARE
@tb_dbSize TABLE
(
id int identity(1,1) not null PRIMARY key
,size int null
,name sysname null
,database_id int null
,type bit null
,on_drive char(1) null
,f_name sysname null
);
--size = 8K
INSERT INTO @tb_dbSize
SELECT
F.size
,DB.name
,F.database_id
,F.type
,left(F.physical_name,1)
,F.name AS f_name
FROM sys.master_files AS F WITH (NOLOCK)
INNER JOIN sys.databases AS DB WITH (NOLOCK)
ON F.database_id = DB.database_id
--==============================================all database reservedpages generation
declare cur_database cursor local static forward_only read_only
for
select name
from sys.databases with(nolock)
--==============you can filter the system database or not
--where name not in(
--'master'
--,'tempdb'
--,'model'
--,'msdb'
--,'distribution'
--);
open cur_database
fetch next from cur_database into @database_name
while(@@FETCH_STATUS = 0)
begin
SET
@exec_sql = N'USE ' + QUOTENAME(@database_name) + ';'
+ @sql;
exec sys.sp_executesql @exec_sql
,N'@reservedpages int output'
,@reservedpages = @reservedpages output
insert into @tb_database_reservedpages
select db_id(@database_name),@database_name,@reservedpages;
fetch next from cur_database into @database_name
end;
close cur_database
deallocate cur_database
--select * from @tb_database_reservedpages
--============================================end all database reservedpages generation
--====================all database size statistics
;WITH dbSize--type = 0
AS
(
SELECT
database_name = name,
database_id,
on_drive,
fileSize = sum(size)--*8./1024.
FROM @tb_dbSize
WHERE type = 0
GROUP BY name,database_id,on_drive
)
,logSize--type = 1
AS
(
SELECT
database_name = name,
database_id,
on_drive,
fileSize = sum(size)--*8./1024.
FROM @tb_dbSize
WHERE type = 1
GROUP BY name,database_id,on_drive
)
,Size
AS
(
SELECT
database_name = name,
database_id,
dbSize = sum(size)--*8./1024.
FROM @tb_dbSize
GROUP BY name,database_id
)
SELECT
server_name = @@SERVERNAME
,A.database_name
,[dbSize(MB)] = S.dbSize * 8./1024.
,[dataSize(MB)] = A.fileSize * 8./1024.
,[unallocatedSize(MB)] = ltrim(str((case
when A.fileSize/*@dbsize*/ >= rvp.reservedpages/*@reservedpages*/ then
(convert (dec (15,2),A.fileSize/*@dbsize*/) - convert (dec (15,2),rvp.reservedpages/*@reservedpages*/))
* 8192 / 1048576
else 0
end),15,2) + ' MB')
,data_Drive = A.on_drive
,[logSize(MB)] = B.fileSize * 8./1024.
,log_Drive = B.on_drive
FROM dbSize AS A
INNER JOIN logSize AS B
ON A.database_id = B.database_id
INNER JOIN Size AS S
ON A.database_id = S.database_id
INNER JOIN @tb_database_reservedpages AS rvp
ON A.database_id = rvp.database_id
WHERE A.database_name = ISNULL(@db_name,A.database_name)
AND (A.on_drive = ISNULL(@drive,A.on_drive)
OR B.on_drive= ISNULL(@drive,B.on_drive)
)ORDER BY S.dbSize DESC
--=================end all database size statistics
--====================instance summary
;WITH DATA
AS
(select
Size = cast(sum(size)*8./1024./1024. AS decimal(9,2))
,type
FROM @tb_dbSize
GROUP BY type
)
SELECT
server_name = @@SERVERNAME
,[0] + [1] AS [dbSize(GB)]
,[0] AS [dataSize(GB)]
,[1] AS [logSize(GB)]
FROM (
SELECT *
FROM DATA
) AS A
PIVOT
(
sum(size)
FOR type in([0],[1])
) AS B
--=================end instance summary

复制代码

说明

1. 参数@db_name：如果有值表示查看特定数据库信息；如果为null表示查看所有数据库信息

2. 参数@drive：如果有值表示查看特定磁盘驱动器的信息；如果为null表示查看所有磁盘的信息。

3. 本文的目的是快速而准确的定位问题所在，当然我们也可以使用RDP，从Windows窗口查看，但效率上无法相比。

4. 解决Disk Monitor不是本文讨论的范畴(其实也就是在定位问题后，收缩数据库文件)

ITPub博客

MSSQL DBA 常用脚本总结

最新文章