SQL Script to Get Free Space Information where Database Files are located

Leave a comment (0) Go to comments

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.

 SQL Script to Get Free Space Information where Database Files are located sql server Drive Free Space Information Script to Get Free Space Information Drive Free Space Information sql server

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


SQL Script to Get Free Space Information where Database Files are located sql server Drive Free Space Information Script to Get Free Space Information Drive Free Space Information sql server

EOF - SQL Script to Get Free Space Information where Database Files are located, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.