Search This Blog

Wednesday 18 January 2012

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.

Tuesday 17 January 2012

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