This is one of the simplest query that I wrote for one of my consulting customers who asked me. When I was discussing with the customer on some of the best practices of working with SQL Server, I was telling him not to use the autogrowth as a regular feature to use. I wanted the DBA to make sure that the autogrowth never gets called. The immediate question was – how do I know I need to increase my database files? When should I be doing them? In What maintenance cycle should this be done to find Free Space.
It was easy on first call because I immediately opened up the Database properties and show where he can get the valuable information. But I was questioned quickly on how to do the same via a code in the backend so that he can use some automation to this whole exercise.
On sitting with the team for about 5 minutes, this is the code I gave them to use it as handy for this particular scenario.
SELECT SUBSTRING(a.FILENAME, 1, 1) Drive, [FILE_SIZE_MB] = convert(decimal(12,2), round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2), round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2), round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , [FREE_SPACE_%] = convert(decimal(12,2), (convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) * 100)), a.NAME, a.FILENAME FROM dbo.sysfiles a ORDER BY Drive, [Name]
The output of the above query is going to look like below:
Now with this information, now one can easily see how much Free Space one has on a given file and where these files are stored. I am sure each one of you is already using something similar in your environments. It would be great if you can share some of your experiences because I will be surely able to build on top of it to make more sense for customers I meet on a day-today basis.
Reference: Pinal Dave (https://blog.sqlauthority.com)