Online Access Free 070-764 Exam Questions

You are building a stored procedure for a SQL Azure database. The procedure will add multiple rows to a table. You need to design the stored procedure to meet the following requirements:
* If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded.
* If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure.
What should you include in the design?

• A.An implicit transaction that has error handling enabled
• B.An implicit transaction that has XACT_ABORT enabled
• C.An explicit transaction that has XACT_ABORT disabled
• D.An explicit transaction that has error handling enabled

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

The backup strategies for each database are described in the following table.

Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages.
You must display the following information about the corrupted pages:
* database name
* impacted file id
* impacted file physical name
* impacted page id
* event type that identifies the error type
* error count
Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:
* Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).
* Capture queries based on resource consumption.
* Use a stale query threshold value of 60 days.
The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were automatically created and have not been updated for 30 days. The update should be based on all data in the table.
You need to recover SalesDb2 and SalesDb3 while minimizing the data loss.
In the table below, identify the recovery strategy you should use for each database.
NOTE: Make only one selection in each column. Each correct selection is worth one point.

Correct Answer:

Explanation

SalesDB2:
Row data are torn (error 824)
How to Fix Torn Pages
Run DBCC checkdb see for inconsistencies
Check your error logs first and then restore your last backups and transaction logs.
All backups are lost.
SalesDB3:
Checksum issues for data pages.
FEPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/manage-the-suspect-pages-table-sql-ser

You deploy a database by using SQL Server 2012.
The database contains a table named Table1.
You need to recommend a solution that meets the following requirements:
* Stores the most recent data from Table1 by using the fastest storage solution possible.
* Stores the historical data from Table1 by using a slower storage solution.
What should you recommend?

• A.change data capture
• B.a database snapshot
• C.table partitioning
• D.partitioned views

You administer a Microsoft SQL Server 2016 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load.
You need to limit the number of cores that handle I/O.
What should you configure?

• A.Lightweight pooling
• B.Processor affinity
• C.I/O affinity
• D.Max worker threads

You have a SQL Server 2012 database named DB1.
You plan to import a large number of records from a SQL Azure database to DB1.
You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation.
What should you include in the recommendation?

• A.a new partitioned table
• B.the bulk-logged recovery model
• C.a new filegroup
• D.the full recovery model
• E.a new log file