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?
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?
To determine the size of the transaction log file for each distribution in Azure Synapse Analytics, you should query the sys.dm_pdw_nodes_os_performance_counters dynamic management view on the master database. This view provides performance counter data, including the size of the transaction log files used by each distribution. The specific information needed can be found by looking for counters related to 'Log File(s) Used Size (KB)'.
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)'
but you don't need it from master, just DW1
This is correct. The explanation is given in the link provided above.
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
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).
Agreed with A.
-- 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
This query returns the transaction log size on each distribution.
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
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.
Answer is On the master database, execute a query against the sys.dm_pdw_nodes_os_performance_counters dynamic management view. 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)' References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage-monitor
https://docs.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.
ChatGPT: SELECT name, type_desc, size * 8 / 1024 AS size_in_MB FROM sys.database_files WHERE type = 1; -- Type 1 corresponds to log files
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.
Should be D
A is wrong it applies for SQL server and non distributed non MPP database.. question clearly says per distribution and synapse
the question is about distribution, so D should be answer.
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
i think "D"