SQL Server Training | Tutorials | How to's

Recently I working on a server, where I need to get free drive space information on the Server. I can check this information either by My Computer (Windows Explorer) or by master..xp_fixeddrives, but this will give information about all drives, regardless whether SQL Server database files are hosted on those drives or not as Show below.

Script Requirement

Need to know the free space information, only for those drives, which hosts SQL Server Database files. Here is a quick script for getting this information which will give us

• Free Space Information in MB / % where the SQL Server Database files are located.

--- Get Free Drive Space Information where SQL Server Database Files are located
set nocount on

declare @db_name varchar(50),
        @sql varchar(500),
        @drive_letter char(1),
        @free_space varchar(20)

create table tempdb.dbo.db_drives
(
    db_drive_letters char(1)
)

create table tempdb.dbo.server_drives
(
    server_drive_letters char(1),
    free_space int
)

set @sql = 'insert into tempdb.dbo.server_drives exec master.dbo.xp_fixeddrives'
    execute (@sql)

declare db_cursor cursor for
select rtrim(name) from [master].[dbo].[sysdatabases]
    where (name not in ('model', 'Northwind', 'Pubs'))
    and (status & 32 <> 32)
    and (status & 64 <> 64)
    and (status & 128 <> 128)
    and (status & 256 <> 256)
    and (status & 512 <> 512)
    and (status & 1024 <> 1024)
    and (status & 2048 <> 2048)
    and (status & 4096 <> 4096)
    and (status & 32768 <> 32768)
open db_cursor
fetch db_cursor into @db_name
while @@fetch_status = 0
begin
    set @sql = 'insert into tempdb.dbo.db_drives select substring(filename, 1, 1) from ' + @db_name + '.dbo.sysfiles'
    execute (@sql)
    fetch db_cursor into @db_name
end

close db_cursor
deallocate db_cursor

declare drive_space_report cursor for
    select distinct db_drive_letters, free_space
    from tempdb.dbo.db_drives a inner join tempdb.dbo.server_drives b
    on (a.db_drive_letters = b.server_drive_letters)
open drive_space_report
fetch drive_space_report into @drive_letter, @free_space
while @@fetch_status = 0
begin
    if @free_space < 100
        RAISERROR ('There is only %S MB of free space remaining on Drive %s', 16, 1, @free_space, @drive_letter) WITH LOG
    if @free_space > 100
        print 'There is ' + @free_space + ' MB of free space remining on Drive ' + @drive_letter
    fetch drive_space_report into @drive_letter, @free_space
end
close drive_space_report
deallocate drive_space_report

drop table tempdb.dbo.db_drives
drop table tempdb.dbo.server_drives

set nocount off

OUTPUT