T-SQL to Get Drive Space Info

Problem


I was trying to get disk information like total space and free using t-sql but extended stored procedure xp_fixeddrives provide only free space info(see screen shot below).

Solution

Powershell can fetch disk information very easily. T-SQL script below uses powershell to get this information.

Pre-requisite:  xp_cmdshell  and Powershell 1.0

To enable xp_cmdshell  execute the following script.

sp_configure 'show advanced options', 1
Go 
Reconfigure 
Go 
sp_configure 'xp_cmdshell',1 
go 
Reconfigure 
Go

Create new store procedure sp_spaceinfo by executing following script.


USE [master]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_spaceinfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_spaceinfo]
GO


USE [master]
GO


CREATE PROCEDURE [dbo].[sp_spaceinfo] 
AS


DECLARE @psinfo TABLE(data  NVARCHAR(100)) ;
INSERT INTO @psinfo
EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;
DELETE FROM @psinfo WHERE data is null  or data like '%DeviceID%' or data like '%----%';
update @psinfo set data = REPLACE(data,' ',',');


;With DriveSpace as (


select SUBSTRING(data,1,2)  as [Drive], 
replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',',
(substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
as [FreeSpace]
, 
replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%', 
(substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
as [Size]
from @psinfo
) 
SELECT Drive, convert(dec( 6,2),CONVERT(dec(17,2),FreeSpace)/(1024*1024*1024)) as FreeSpaceGB, convert(dec( 6,2),CONVERT(dec(17,2), size)/(1024*1024*1024)) as SizeGB
FROM DriveSpace;
GO

OS output

sp_spaceused output

Tested On

I have tested this code on SQL Server 2008 R2 , SQL Server 2008 and SQL Server 2005.
Leave you feedback for encouragement.

SQL Server Database Size Info

This code is to get Database size info in one recordset.


Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] int
)
INSERT INTO @FileSpace  EXEC sp_MSforeachdb 'USE ? SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'')  as spaceused from sys.sysfiles';

With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(

SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM  
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(4,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as  DataFileSizeMB,
convert(dec(4,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(4,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100)  as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT  sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName)  as D
CROSS APPLY (SELECT  sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName)  as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB

SQL Server Database Backup Info

There are two scripts one for getting only backup dates of each database and other one also fetch backup size.

First ,

Use [Master]

Go

select

S.name as [DatabaseName],

S.recovery_model_desc as [RecoveryModel],

S.state_desc [DatabaseState],

S.create_date as [DatabaseCreatedDate],

b.[Last_Full_Backup_Date] as [LastFullBackupDate],

L.Last_Log_Backup_Date as [LastLogBackupDate],

I.[Last_Diff_Backup_Date] as [LastDiffBackupDate]

from

sys.databases S

left outer join

(select database_name, max(backup_finish_date) as [Last_Full_Backup_Date] from msdb.dbo.backupset

Where type='D'

group by database_name) b on ( s.name =b.database_name)

Left outer join

(select database_name, max(backup_finish_date) as [Last_Log_Backup_Date] from msdb.dbo.backupset

Where type='L'

group by database_name) L on ( s.name =L.database_name)

Left outer join

(select database_name, max(backup_finish_date) as [Last_Diff_Backup_Date] from msdb.dbo.backupset

Where type='I'

group by database_name) I on ( s.name =I.database_name)

order by

s.name

Second,

Use [Master]

Go

select

S.name as [DatabaseName],

S.recovery_model_desc as [RecoveryModel],

S.state_desc [DatabaseState],

S.create_date as [DatabaseCreatedDate],

F.[Last_Full_Backup_Date] as [LastFullBackupDate],

F.backup_size,

L.Last_Log_Backup_Date as [LastLogBackupDate],

L.backup_size as [LogBackupSize],

I.[Last_Diff_Backup_Date] as [LastDiffBackupDate],

I.backup_size as [DiffBackupSize]

from

sys.databases S

outer apply

(select top 1 database_name, backup_finish_date as [Last_Full_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='D' and database_name =s.name order by backup_finish_date desc ) F

outer apply

(select top 1 database_name, backup_finish_date as [Last_Log_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='L' and database_name =s.name order by backup_finish_date desc ) L

outer apply

(select top 1 database_name, backup_finish_date as [Last_Diff_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='I' and database_name =s.name order by backup_finish_date desc ) I

order by

s.name