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

15 comments:

RIA Institute of Technology said...
This comment has been removed by the author.
RIA Institute of Technology said...

This is a very informative blog and it is very useful for people who work on SQL.

Best Software Courses in Bangalore
Best Sofware classes in Bangalore

UNKNOWN said...

Thanks for one marvelous posting! I enjoyed reading it; you are a great author. I will make sure to bookmark your blog and may come back someday. I want to encourage that you continue your great posts, have a nice weekend!

Data Science Training in Bangalore

Stephanie Schoolcraft said...

Despite working with a complex workflow top product design firms, they were able to improve and understand the process very quickly.

Darcy Sullivan said...

They also branded the website, which was required to have a seamless UX/UI while maintaining sophisticated functionalities.
brand building firm

anonymas said...

Organized and communicative mobile design agencies, they are efficient time managers who spare no effort to deliver practical solutions

Charles Null said...

Communication was handled through appropriate channels. They were very reliable, open, and proactive.
branding design agency

Splegalnurse said...

Each time I used to always check blog posts within the first hours in the break of day, because I like to get information increasingly more.
UI UX design agency

Karen Rogers said...

Cool blog site friend I'm about to suggest this to all my listing contacts.
companies that create logos

Jamie Mack said...

This is one of the most important blogs that I have seen, keep it up!
web agency

Stephanie Clifton said...

Wonderful, just what a blog it is! This blog has provided the helpful data to us continue the good work.
UI UX company

Harry Jack said...

You fully match our expectation and the selection of our data.
UI UX design agency

AxesgeeksDigitalmarketing said...

Osm blog site dear I'm about to suggest this to all my listing contacts.
Digital Marketing company in noida


bigpond webmial setup (08) 6102 0008 said...


Nice blog site dear I'm about to suggest this to all my listing contacts.
Bigpond webmail support

ravsitsol said...

Thank you, for providing these tips. Personally,
I believe the best strategy is to repurpose the old content into a new useful one.

Post a Comment

Ads