Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 191–195 (page: 40)

Updated On: 10-Feb-2026

You have an Azure subscription that contains an Azure SQL database named db1.

You need to implement SQL insights for db1.

Which two resources should you create first? Each correct answer presents part of the solution.

Note: Each correct selection is worth one point.

  1. a storage account
  2. a virtual machine
  3. an Azure logic app
  4. an Azure function
  5. a Log Analytics workspace

Answer(s): A,E



You have an Azure subscription that contains the resources shown in the following table.



App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity.

You need to modify db1 to resolve the issues experienced by App1 as soon as possible, without considering immediate costs.

What should you do?

  1. Enable automatic tuning for db1.
  2. Increase the number of vCores allocated to db1.
  3. Decrease the auto-pause delay for db1.
  4. Disable auto-pause delay for db1.

Answer(s): D

Explanation:

Applications that connect to a cloud service such as Azure SQL Database should expect periodic reconfiguration events and implement retry logic to handle these errors instead of surfacing application errors to users.

Auto-pause delay
In the Azure Serverless architecture, the database automatically pauses if it is inactive during the specified duration. By default, it is enabled and set to 1 hour (3600 seconds).
Note: Serverless compute tier
The serverless compute tier for single databases in Azure SQL Database is parameterized by a compute autoscaling range and an auto-pause delay. The configuration of these parameters shapes the database performance experience and compute cost.



The auto-pause delay is a configurable parameter that defines the period of time the database must be inactive before it is automatically paused. The database is automatically resumed when the next login or other activity occurs. Alternatively, automatic pausing can be disabled.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview https://www.sqlshack.com/automatic-pause-and-resume-an-azure-sql-database/



HOTSPOT (Drag and Drop is not supported)

You have an Azure SQL database named DB1 that contains a table named Table1.

You run a query to load data into Table1.

The performance metrics of Table1 during the load operation are shown in the following exhibit.



Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: perform query tuning
Implementation considerations
Use memory-optimized tables for your core transaction tables, that is, the tables with the most performance- critical transactions. Use natively compiled stored procedures to optimize execution of the logic associated with the business transaction. The more of the logic you can push down into stored procedures in the database, the more benefit you see from In-Memory OLTP.

To get started in an existing application:

1. Use the transaction performance analysis report to identify the objects you want to migrate.
2. Use the memory-optimization and native compilation advisors to help with migration.

Box 2: an In-Memory OLTP non-durable
Example: tempdb object replacement
Use non-durable tables and memory-optimized table types to replace your traditional tempdb based structures, such as temporary tables, table variables, and table-valued parameters (TVPs).

Memory-optimized table variables and non-durable tables typically reduce CPU and completely remove log IO, when compared with traditional table variables and #temp table.

Note: In-memory technologies enable you to improve performance of your application, and potentially reduce cost of your database.
In-Memory OLTP
In-Memory OLTP technology provides extremely fast data access operations by keeping all data in memory. It also uses specialized indexes, native compilation of queries, and latch-free data-access to improve performance of the OLTP workload. There are two ways to organize your In-Memory OLTP data:

* Memory-optimized rowstore format where every row is a separate memory object. This is a classic In- Memory OLTP format optimized for high-performance OLTP workloads. There are two types of memory- optimized tables that can be used in the memory-optimized rowstore format:

Durable tables (SCHEMA_AND_DATA) where the rows placed in memory are preserved after server restart. This type of tables behaves like a traditional rowstore table with the additional benefits of in-memory optimizations.
Non-durable tables (SCHEMA_ONLY) where the rows are not-preserved after restart. This type of table is designed for temporary data (for example, replacement of temp tables), or tables where you need to quickly load data before you move it to some persisted table (so called staging tables).

* Memory-optimized columnstore format where data is organized in a columnar format


Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage- scenarios https://learn.microsoft.com/en-us/azure/azure-sql/in-memory-oltp-overview



DRAG DROP (Drag and Drop is not supported)

You have a database named db1.

The log for db1 contains the following entry.



You need to ensure that db1 can process transactions.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Select and Place:

  1. See Explanation section for answer.

Answer(s): A

Explanation:






Step 1: Remove db1 from the availability group.

Step 2: Shrink the transaction log file.
You can use SQL Server Management Studio or Transact-SQL to shrink a data or log file.

Example:
SQL

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Incorrect:
* Back up the transaction log file.
Backup the Log file to free up space within the file (logically/virtually).

Step 3: Add db1 back to the availability group.

Note:
Symptoms
Consider the following scenario:

You have Microsoft SQL Server 2012 or a later version installed on a server. The instance of SQL Server is a primary replica in Always On Availability Groups environment. The autogrow option for transaction log files is set in SQL Server. In this scenario, the transaction log may become large and run out of disk space or exceed the MaxSize option set for the transaction log at the primary replica and you receive an error message that resembles the following:

Error: 9002, Severity: 17, State: 9. The transaction log for database '%.*ls' is full due to 'AVAILABILITY_REPLICA'

Cause
This occurs when the logged changes at primary replica are not yet hardened on the secondary replica.

Workaround
After you identify the secondary database that makes this occur, try one or more of the following methods to work around this issue temporarily:

* Take the database out of the availability group for the offending secondary.

Note
This method will result in the loss of the High Availability/Disaster Recovery scenario for the secondary. You may have to set up the Availability Group again in the future.

* If the redo thread is frequently blocked, disable the Readable Secondary feature by changing the ALLOW_CONNECTIONS parameter of the SECONDARY_ROLE for the replica to NO.

Note

This will prevent users from reading the data in the secondary replica which is the root cause of the blocking. Once the redo queue has dropped to an acceptable size, consider enabling the feature again.

* Enable the autogrow setting if it is disabled and there is available disk space.

* Increase the MaxSize value for the transaction log file if it has been reached and there is available disk space.

* Add an additional transaction log file if the current one has reached the system maximum of 2 TB or if additional space is available on another available volume.


Reference:

https://learn.microsoft.com/en-us/troubleshoot/sql/availability-groups/error-9002-transaction-log- large https://thecloudtechnologist.com/2015/03/18/shrinking-sql-log-files-in-an-availability-group-cluster-or-database- mirror/



You have the following resources:

15 SQL Server on Azure Virtual Machines instances

20 Azure SQL databases

You need to recommend a solution to centrally monitor the resources for security vulnerabilities.

What should you include in the recommendation?

  1. database audits
  2. Microsoft Defender
  3. SQL insights
  4. Azure SQL Auditing

Answer(s): B



Viewing page 40 of 85
Viewing questions 196 - 200 out of 470 questions


Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!