Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find Large Files on OS - Results of DIR to Table

By Congruent Influence,

Purpose: To identify files over 40 GB that are in the filepath sprcified and its subdirectories. We had an issue where backups would fail sporadically with an error indicating they were out of space. However, when reviewing the disk in the morning, there was clearly enough space for a backup to complete. The intention with this is to schedule it to run hourly to identify a new large file that shows up that would hinder the backups completion. Put the results of DIR in a table for easy querying.

Alternative Usage: Modify the code to be able to pass in a size value to find out the largest files for a given path. How often have you asked the question, what is using up all the space on my D drive? This would allow you to find that out.

PreRequisites: Create a database named utilities or else change the database name in the script. Create a table named LargeFiles in Utilities or else rename it. The script for this table creation is commented out in the header. After compiling and executing, do a select on utilities.dbo.largefiles to see what it found.

Cautions: see various other articles on the usage of xp_cmdshell

I welcome enhancement suggestions and hope this will start you on the path to accomplishing what you need to by putting the results of the DIR command into a table.

Total article views: 1180 | Views in the last 30 days: 2
Related Articles


Backup would not work to back up


Database Backup fails due to lack of disk space

Database Backup job created on SQL Server 2005 Maintenance Plan, fails due to lack of disk space


Managing Free Space

A new article from Paul Els looks at managing free space on your database servers by finding detache...


While taking backup of MSDB database, in report log it is showing shortage of space. actually we are having 60+ Gb free space available on that drive.

While taking backup of MSDB database, in report log it is showing shortage of space. actually we are...





Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones