Prev: Date filtering
Next: Capturing First Error Message
From: SQL on 21 Jan 2010 01:26
Thanks, very useful info



Victor Arquero wrote:

Get Database File Space Information
06-Jan-10

Hi David,

Good day. You may try this ...

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)

Hope this helps you :)

Vic

Previous Posts In This Thread:

On Friday, March 07, 2008 2:28 PM
Davi wrote:

free space in data files
I am using SQL Server 2005 and multiple data files spread across different
disk drives. I would like to find out the consumed and empty space in each
file. Can anyone suggest me the way to do so?

Thanks,
David

On Friday, March 07, 2008 2:40 PM
Aaron Bertrand [SQL Server MVP] wrote:

How aboutDBCC SHOWFILESTATS;This will show Total and Used extents in each data
How about

DBCC SHOWFILESTATS;

This will show Total and Used extents in each data file. Multiply by 64 to
get KB (8 pages per extent x 8kb per page).

A

On Wednesday, January 06, 2010 11:22 PM
Victor Arquero wrote:

Get Database File Space Information
Hi David,

Good day. You may try this ...

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)

Hope this helps you :)

Vic

On Thursday, January 21, 2010 1:25 AM
SQL Enthusiast wrote:

ttest
test


Submitted via EggHeadCafe - Software Developer Portal of Choice
Transfer Session State From Classic ASP to ASP.NET
http://www.eggheadcafe.com/tutorials/aspnet/9278c9c2-c05a-4d52-ac02-436a9db5c402/transfer-session-state-fr.aspx
 | 
Pages: 1
Prev: Date filtering
Next: Capturing First Error Message