Breaking News: Grepper is joining You.com. Read the official announcement!

sql server query database space available

MzanziLegend answered on August 3, 2021 Popularity 9/10 Helpfulness 4/10

answer sql server query database space available

sql server query database space available

Comment

-1

--DISK SPACE

DROP TABLE #tmp
CREATE TABLE #tmp (COL1 VARCHAR(20),COL2 INT)
INSERT INTO #tmp
EXEC master..xp_fixeddrives
SELECT col1 AS drive, col2/1000 AS free_spce_in_gb FROM #tmp ORDER BY 2

DROP TABLE #output
create table #output (output nvarchar(255) null)
insert #output (output) EXEC master..xp_cmdshell 'PowerShell.exe -noprofile -command "get-WmiObject Win32_LogicalDisk  | Format-Table -Property DeviceID,FreeSpace"'
select LEFT(output,3) AS DRIVE, LTRIM(RTRIM(RIGHT(OUTPUT,LEN(OUTPUT)-3))) AS FREE_SPACE from #output where output LIKE '%C:%'

--SERVER SPACE

SELECT DISTINCT 
	dovs.volume_mount_point AS Drive,
	DB_NAME(dovs.database_id) DBName,
	dovs.logical_volume_name AS LogicalName,
	(CONVERT(INT,dovs.available_bytes/1048576.0) / 1000) AS FreeSpaceInGB
FROM
	sys.master_files mf
	CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY 
	FreeSpaceInGB ASC;

--DB SPACE
SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128.0 AS CurrentSizeMB,  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);

Popularity 9/10 Helpfulness 4/10 Language sql

Source: www.sqlshack.com

Tags: database space sql sql-server

Link to this answer
Share Copy Link

Contributed on Aug 03 2021

MzanziLegend

0 Answers Avg Quality 2/10

sql server query database space available

Contents

More Related Answers

sql server query database space available

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.