0

I'm trying to get the database_size value of SQL Server (Data Warehouse)

As far as i know, we have sp_spaceused that can be used to check database_size

But i couldn't find any solution to help grep the number of database_size value

Another solution is not using spaceused, but since Azure DWH doesn't allow sys.master_file, other solution also didn't work.

Any help would be greatly appreciated!

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
The One
  • 2,261
  • 6
  • 22
  • 38
  • 1
    Have a look into this thread https://stackoverflow.com/questions/18014392/select-sql-server-database-size it might help you. – Suraj Kumar Jan 24 '19 at 08:49
  • 1
    Thanks Suraj.I'll check it – The One Jan 24 '19 at 08:51
  • Unfortunately, all of the answers mentioned using sys.master_file which is not allowed in my case. – The One Jan 24 '19 at 08:52
  • Try this thread also https://stackoverflow.com/questions/13296150/calculate-size-of-sql-azure-database – Suraj Kumar Jan 24 '19 at 09:02
  • Oops, Catalog view 'dm_db_partition_stats' is not supported in this version. '(( – The One Jan 24 '19 at 09:06
  • Per [the docs](https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql), `sys.dm_db_partition_stats` is called `sys.dm_pdw_nodes_db_partition_stats` in Azure SQL Data Warehouse. – Jeroen Mostert Jan 24 '19 at 12:21

3 Answers3

0

When you will run the query EXEC sp_helpdb you will get the entire database size. Here also you can pass any specific database name. No matter in which database you are currently.

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
  • 1
    Thanks for your answer. I tried your suggestion, and got this error Stored procedure sp_helpdb does not exist or is not supported. I guess Azure DWH is pretty limited in term of what we can do :( – The One Jan 24 '19 at 08:58
  • That might be some limitation and I have checked in the local machine. – Suraj Kumar Jan 24 '19 at 08:59
0

There are a couple of DMVs you can use to get this kind of information: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql?view=sql-server-2017

Thailo
  • 1,314
  • 7
  • 13
0

Try

declare @dbsize as bigint
declare @logsize as bigint

select 
  @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles;

select dbSizeInMB = cast((@dbsize + @logsize)*8/1024.0  AS decimal(10,2) );
Gabriele Petrioli
  • 191,379
  • 34
  • 261
  • 317