Quick Way To Find File Sizes For Your Database!

Hello and welcome to my blog!...

Here you will find what I like to call "nuggets" that I come across while working with SQL Server and other tools!...

Today I am going to share with you a quick way in T-SQL to determine the file sizes in your database. The script below will work in SQL Server 2000, 2005, 2008 and 2008 R2. You will need to run this in the context of the database you want the file sizes for.

SELECT FILEID,
CONVERT(decimal(12,2),ROUND([size]/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty([name],'SpaceUsed')/128.000,2))
as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND(([size]-fileproperty([name], 'SpaceUsed' )) /
128.000,2)) as [FREESPACEINMB],
[name] as [DATABASENAME],
[filename] as [FILENAME]
FROM dbo.sysfiles

Let us know if you know of another way to get file sizes fast!