Finding disk free space and other file level information for SQL Files
Posted by Sourabh Agarwal on March 13, 2012
Recently a friend of mine, Rahul Soni in his blog wrote a .Net code to retrieve the file information and other drive information for all SQL Server files.
While discussing this, he wanted to know if this can be done using T-SQL or not, and so the blog article follows.
Most of the file level information can be retrieved from the catalog View Sys.Master_files. To run queries against this view and see the records, the user would need the following permissions
- CREATE DATABASE
- ALTER ANY DATABASE
- VIEW ANY DEFINITION
The catalog view returns information about all the files for all the databases present in SQL Server. Details about the view can be found in SQL Server Books Online.
Additionally, to find the free space information on the Drives, I used to xp_fixeddrives extended stored procedure.
Included below is the code for the stored procedure which I wrote to get this information
1: USE [master]
2: GO
3: /****** Object: Stored Procedure [dbo].[usp_getFileinformation] Script Date: 03/13/2012 20:13:15 ******/
4: SET ANSI_NULLS ON
5: GO
6: SET QUOTED_IDENTIFIER ON
7: GO
8: ALTER procedure [dbo].[usp_getFileinformation]
9: As
10: create table #fixeddrives
11: (
12: Drive varchar(2),
13: FreeSpaceMB int
14: )
15:
16: insert into #fixeddrives(Drive,FreeSpaceMB) exec xp_fixeddrives
17: select
18: DB_NAME(database_id) As DatabaseName, type_desc as FileType,
19: name as [FileName], physical_name as FileLocation,
20: (size*8) as CurrentSizeInKB,
21: case max_size
22: when -1 then null
23: else max_size
24: end As MaxFileSize,
25: Case
26: when is_percent_growth = 1 then CAST(growth AS varchar(5)) + '%'
27: else CAST(growth AS varchar(5)) + ' MB'
28: End FileGrowth,
29: SUBSTRING(physical_name, 1,1) As Drive
30: into #temp from master.sys.master_files
31:
32: select t.DatabaseName, t.FileType, t.FileName, t.FileLocation, t.CurrentSizeInKB, t.MaxFileSize,t.FileGrowth,
33: fd.FreeSpaceMB As FreeSpaceOnDrive_MB
34: from #temp t
35: inner join #fixeddrives fd on fd.Drive = t.Drive
This stored procedure returns the following information.
