SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Get Database Size. Advice on my code.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Constraint Violating Yak Guru

450 Posts

Posted - 11/23/2012 :  09:34:51  Show Profile  Reply with Quote

I need to get the following data from a database:

1 - Data Size;
2 - Log Size;
3 - Total Filestream Size;

And if possible the free size in (1), (2) and (3).

I tried the following:

  DB_NAME(DB_ID()) AS DatabaseName, 
  name AS [FileName], 
  size/128.0 AS Size, 
  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSize
FROM sys.database_files;

This returns the following:

DatabaseName FileName Size FreeSize
TestDB TestDB_Data 40.000000 36.937500
TestDB TestDB_Log 4.000000 3.593750
TestDB TestDB_Filestream 231.468750 228.406250

The problem is that I get the data by file.

While I would like to simply get 6 variables:

DataSize = 40.000000
LogSize = 4.000000
FilestreamSize = 231.468750

DataFreeSize = 36.937500
LogFreeSize = 3.593750
FilestreamFreeSize = 228.406250

Thank You,

  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000