How to find the Data and Log File location of all the Databases

I received a SCOM space alert for one of my SQL Server 2008 instance and found that that one of my Data Drive is almost full.

D: disk is at or near capacity. You may need to delete some files

I know that databases are not growing that fast. I released some space by clear the log files, but it didn’t help much.

When I looked into the data and log file folders I see log of files with very old date. It looks like somebody got deleted the database but never cleared the files.

In this situation make sure you first get a list of data and log files with location using below SQL Query then proceed with cleaning the old files.

SELECT Name,Physical_Name AS File_Location
FROM sys.master_files

To find disk space allocate and fee space in SQLServer use below command.

SELECT Name, Filename,

CONVERT(Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],

CONVERT(Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],

CONVERT(Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)]

FROM dbo.sysfiles a (NOLOCK)

The above SQL provided Name of the Data and log file, File name with Location, Currently Allocated space, Space Used and Available space in SQL Server.

Regards

Satishbaub Gunukula

http://www.sqlserver-expert.com