如何知道日志文件大小和驱动器空间，数据文件大小，驱动器空间 [英] how to know log file size and drive space, data file size,drive space

问题描述

我有多个服务器并且有多个数据库和驱动器，请引导或共享任何TSQL或电源shell脚本以了解多个服务器和dbs的关注

I have multiple server and having multiple database and drive, pls guide or share any TSQL or power shell script to know about following for multiple servers and dbs

日志文件大小，驱动器空间填充和空闲，数据文件大小等。

log files size, drive space fill and free, data file size etc.

Thx

iffi

推荐答案

以下程序为您提供有关DB，日志，免费的信息我用这个循环Python的程序来向我展示整个网络/服务器的情况。但是，您可以使用自己喜欢的代码来获得相同的结果。 

The procedure below gives you information about DBs, Logs, free space, etc. I use this procedure looped in Python to show me the entire network/servers situation. However, you can use your favorite code to achieve the same result. 

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[ReadSize]    Script Date: 11/03/2019 12:59:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE Procedure [dbo].[ReadSize] as 

SET NOCOUNT ON
DECLARE @Kb float
DECLARE @PageSize float
DECLARE @SQL varchar(2000)

SELECT @Kb = 1024.0
SELECT @PageSize=v.low/@Kb FROM master..spt_values v WHERE v.number=1 AND v.type='E'

IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL
	DROP TABLE #FileSize
CREATE TABLE #FileSize (
	DatabaseName sysname,
	FileName sysname,
	FileSize int,
	FileGroupName sysname,
	LogicalName sysname
)

IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL
	DROP TABLE #FileStats
CREATE TABLE #FileStats (
	FileID int,
	FileGroup int,
	TotalExtents int,
	UsedExtents int,
	LogicalName sysname,
	FileName nchar(520)
)

IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL
	DROP TABLE #LogSpace
CREATE TABLE #LogSpace (
	DatabaseName sysname,
	LogSize float,
	SpaceUsedPercent float,
	Status bit
)

INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)')

DECLARE @DatabaseName sysname

DECLARE cur_Databases CURSOR FAST_FORWARD FOR
	SELECT DatabaseName = [name] FROM dbo.sysdatabases ORDER BY DatabaseName
OPEN cur_Databases
FETCH NEXT FROM cur_Databases INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
  BEGIN
	SET @SQL = '
USE [' + @DatabaseName + '];
DBCC showfilestats;
INSERT #FileSize (DatabaseName, FileName, FileSize, FileGroupName, LogicalName)
SELECT ''' +@DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]
 FROM dbo.sysfiles sf;
'

	INSERT #FileStats EXECUTE (@SQL)
	FETCH NEXT FROM cur_Databases INTO @DatabaseName
  END

CLOSE cur_Databases
DEALLOCATE cur_Databases

SELECT
	DatabaseName = fsi.DatabaseName,
	FileGroupName = fsi.FileGroupName,
	LogicalName = RTRIM(fsi.LogicalName),
	FileName = RTRIM(fsi.FileName),
	FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),
	UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),
	FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),
	[FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2))
 FROM #FileSize fsi
 LEFT JOIN #FileStats fs
	ON fs.FileName = fsi.FileName
 LEFT JOIN #LogSpace ls
	ON ls.DatabaseName = fsi.DatabaseName
 ORDER BY 8,3
GO

如果这篇文章帮助你，请标记为答案

Please mark as answer if this post helped you

这篇关于如何知道日志文件大小和驱动器空间，数据文件大小，驱动器空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！