我有一个驻留在VPS中的Server 2012实例。我不能直接登录VPS的Windows。我知道在那个VPS上安装了多个实例。
我有一个查询,它显示每个DB的文件名、文件大小、已使用的和未使用的空间。通过他们的文件路径,我可以看到它们并不都在同一个分区上(IDK为什么基础设施管理员创建了多个分区.)。
我需要看到每个分区的大小和空闲空间。是否有一种无需在Windows上登录就可以从SQL Server中的查询中找到这一点的方法?
发布于 2015-11-12 11:48:32
我在SQL 2008R2和SQL server 2005上对我的服务器使用以下查询:
它包括在脚本中启用和配置启用Ole自动化过程,然后使用xp_fixeddrives收集信息。
看看下面是否有帮助。
/*******************************************************/
/* Enabling Ole Automation Procedures */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*******************************************************/
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,
TotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive
OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize', @TotalSize OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo
@odrive UPDATE #drives SET TotalSize=@TotalSize/@MB WHERE
drive=@drive FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT @@Servername
SELECT
drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY drive
DROP TABLE #drives
GO发布于 2015-11-12 13:34:33
select @@SERVERNAME
,UPPER(dovs.volume_mount_point) AS [drive]
,max(CONVERT(BIGINT,dovs.available_bytes/1048576.0)) AS [Free(MB)]
,max(CONVERT(BIGINT,dovs.total_bytes/1048576.0)) AS [Total(MB)]
,max(CAST(CONVERT(BIGINT,dovs.available_bytes/1048576.0)/(CONVERT(BIGINT,dovs .total_bytes/1048576.0)*1.0)*100 AS INT)) AS [Free(%)]
FROM sys.master_files as mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) as dovs
GROUP BY dovs.volume_mount_pointhttps://dba.stackexchange.com/questions/120841
复制相似问题