You have an enterprise data warehouse in Azure Synapse Analytics named DW1 on a server named Server1. You need to determine the size of the transaction log file for each distribution of DW1. What should you do?
A.
On DW1, execute a query against the sys.database_files dynamic management view.
B.
From Azure Monitor in the Azure portal, execute a query against the logs of DW1.
C.
Execute a query against the logs of DW1 by using the Get-AzOperationalInsightsSearchResult PowerShell cmdlet.
D.
On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view.
The question asks for transaction log size on each distribution. The correct answer is D: Link below: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor
-- Transaction log size
SELECT
instance_name as distribution_db,
cntr_value*1.0/1048576 as log_file_size_used_GB,
pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'
D is totally correct. Link has this very clearly mentioned
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor
```
SELECT
instance_name as distribution_db, pdw_node_id,
cntr_value*1.0/1048576 as log_file_size_used_GB
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'
```
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-transaction-log-size
ChatGPT 4o
The sys.dm_pdw_nodes_os_performance_counters dynamic management view provides performance counter information for each node in your Synapse Analytics instance. This includes metrics related to the transaction log file.
sys.dm_pdw_nodes_os_performance_counters DMV in Azure Synapse Analytics does not provide information about the size of the transaction log file for each distribution of the data warehouse; it provides CPU utilization, memory usage, disk I/O rates, and network traffic at the node level.
To obtain information about the size of transaction log files, we can use sys.dm_db_file_space_usage or sys.database_files.
Table sys.dm_pdw_nodes_os_performance_counter contains information about current size of file log each distribution.
You can use sys.database_files to determine size of file log of DW1 (each distribiution the same).
-- Transaction log size
SELECT
instance_name as distribution_db,
cntr_value*1.0/1048576 as log_file_size_used_GB,
pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-transaction-log-size
Probably A and D are correct, but I would choise D, because it's clearly described as the question:
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor
Monitor transaction log size
The following query returns the transaction log size on each distribution. If one of the log files is reaching 160 GB, you should consider scaling up your instance or limiting your transaction size.
-- Transaction log size
SELECT
instance_name as distribution_db,
cntr_value*1.0/1048576 as log_file_size_used_GB,
pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-transaction-log-size
D. See this article https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-transaction-log-size
According to the documentation:
"For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files."
A seems enough, I am not sure it gives the results for each distribution but it seems so.
This section is not available anymore. Please use the main Exam Page.DP-203 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Saransundar
Highly Voted 2 years, 12 months agolearnwell
11 months, 1 week agoDavico93
2 years, 11 months agoSaim8711
Highly Voted 2 years, 11 months agoobeheshti
Most Recent 7 months, 1 week agorenan_ineu
8 months, 2 weeks agoahana1074
8 months, 3 weeks agoe56bb91
11 months agoe56bb91
11 months, 1 week agotadenet
1 year, 1 month agoSachmett
1 year, 5 months agoj888
1 year, 4 months agokkk5566
1 year, 9 months ago[Removed]
1 year, 10 months agopavankr
1 year, 11 months agovctrhugo
1 year, 11 months agovctrhugo
1 year, 11 months agoauwia
1 year, 11 months agovctrhugo
1 year, 11 months agoTestingCRM
2 years agoagold96
2 years, 4 months ago