Get Database Size. Advice on my code.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get Database Size. Advice on my code.

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-11-23 : 09:34:51

Hello,

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:


SELECT 
  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,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources