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.
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