CODEWITHSUNDEEP
azureazure-sql-database
R Vigneswaran
R Vigneswaran

Reputation: 51

No data from view sys.resource_usage and sys.resource_stats in azure

When i run the following query in the Azure, I am getting no records. and getting the message "Query succeeded: Affected rows: 0."

1) SELECT * FROM sys.resource_usage where database_name='DB_NAME';

When i run this following query in azure, i get this error , "Failed to execute query. Error: Invalid object name 'sys.resource_stats'." 

2) SELECT * FROM sys.resource_st where database_name='DB_NAME';

Please help me solve this issue, as im trying to track the daily database usage i.e amount of data stored in DB.

Upvotes: 0

Views: 1585

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28930

As Lin mentioned ,You have to connect to Master database to see output of sys.resource_stats

For DBspecific DTU usage , you can use sys.dm_db_resource_stats ..this stores data for every 15 seconds

and your question says

as im trying to track the daily database usage i.e amount of data stored in DB.

you can use below DMV to track that

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;
GO

Upvotes: 0

Hello
Hello

Reputation: 632

I suppose the issue is with your current db context, what does SELECT DB_NAME() return? Is that a user database? As per BOL 'This view is available to all user roles with permissions to connect to the virtual master database.' you'd have to connect to the master database before query against both sys.resource_usage and sys.resource_stats.

Upvotes: 2

Related Questions