T-SQL to get Get Database Files Usage information | SansSQL

Thursday, April 6, 2017

T-SQL to get Get Database Files Usage information

Here is an handy T-SQL script to get Database files usage Information
DECLARE @FreeSpaceThreshold int
SET @FreeSpaceThreshold = 20 -- Change this Threshold Value. Any number between 0 to 100

CREATE TABLE #GetDetails
( 
 ServerName nvarchar(100),
 DatabaseName nvarchar(100),
 LogicalFileName nvarchar(100),
 FileType nvarchar(10),
 FileLocation nvarchar(max),
 [FileSize(MB)] decimal (15,2),
 [UsedSpace(MB)] decimal (15,2),
 [FreeSpace(MB)] decimal (15,2),
 [UsedSpace(%)] decimal (15,2),
 [FreeSpace(%)] decimal (15,2)
 )

EXEC sp_MSforeachdb 'Use [?];
INSERT INTO #GetDetails
SELECT @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName, 
    type_desc AS FileType, 
    physical_name AS FileLocation,
    CAST(size/128.0 AS decimal(15,2)) AS [FileSize(MB)],
    CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [UsedSpace(MB)],
    CAST(size/128.0 AS decimal(15,2)) - CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 AS decimal(15,2)) AS [FreeSpace(MB)],
    CAST(((FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [UsedSpace(%)],
    CAST((((size/128.0) - FILEPROPERTY(name, ''SpaceUsed'')/128.0 ) /  (size/128.0))*100 AS decimal(15,2)) AS [FreeSpace(%)]
FROM sys.database_files'

SELECT * FROM #GetDetails Where [FreeSpace(%)] < @FreeSpaceThreshold
DROP TABLE #GetDetails

Download this script from https://gallery.technet.microsoft.com/T-SQL-to-Get-Database-f16f60c7?redir=0

2 comments:

Riya R said...

Wow, Excellent post. This article is really very interesting and effective. I think its must be helpful for us. Thanks for sharing your informative.
SEO jobs in Hyderabad

Divya madhuri said...

Nice blog with having good information. It’s very useful for everyone. Thanks and keep posting this type of blog
CCNA jobs in Hyderabad .

Post a Comment