Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 121:

  You administer a Microsoft SQL Server 2016 instance.

  You need to stop a blocking process that has an SPID of 64 without stopping other processes.

  What should you do?

  A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64
  B. Restart the SQL Server service.
  C. Execute the following Transact-SQL statement: KILL 64
  D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64'
  C. Execute the following Transact-SQL statement: KILL 64

• Question 122:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  Your company has several Microsoft SQL Server instances. Each instance hosts many databases.

  You observe I/O corruption on some of the instances.

  You need to perform the following actions:

  Identify databases where the PAGE VERIFY option is not set.

  Configure full page protection for the identified databases.

  Solution: You run the following Transact-SQL statement:

  

  For each database that you identify, you run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 123:

  You administer a Microsoft SQL Server 2016 database that includes a table named Application.Events.

  Application.Events contains millions of records about user activity in an application.

  Records in Application.Events that are more than 90 days old are purged nightly.

  When records are purged, table locks are causing contention with inserts.

  You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.

  Which type of solution should you use?

  A. Partitioned tables
  B. Online index rebuild
  C. Change data capture
  D. Change tracking
  A. Partitioned tables

• Question 124:

  DRAG DROP

  You are deploying a Microsoft Azure SQL Database environment:

  You need to configure the environment to meet the following requirements:

  Access to databases must be logged.

  Email alerts must be sent to the database administrator if anomalous activity is detected.

  What should you do? To answer, drag the appropriate solutions to the correct requirements. Each solution may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

  SQL Database Threat Detection runs multiple sets of algorithms which detect potential vulnerabilities and SQL injection attacks, as well as anomalous database access patterns (such as access from an unusual location or by an unfamiliar principal). Security officers or other designated administrators get email notification once a threat is detected on the database. Each notification provides details of the suspicious activity and recommends how to further investigate and mitigate the threat.

• Question 125:

  DRAG DROP

  You plan to install two SQL Server 2014 environments named Environment1 and Environment2. Your company identifies the following availability requirements for each environment:

  Environment1 must have mirroring with automatic failover implemented.

  Environment2 must have Always On with automatic failover implemented.

  You need to identify the minimum number of SQL Server 2014 servers that must be deployed to each environment to ensure that all data remains available if a physical server fails.

  How many servers should you identify? To answer, drag the appropriate number to the correct environment in the answer area.

  Select and Place:

  

  

• Question 126:

  You have a database named DB1.

  Users report that a database application that updates the data in DB1 is unresponsive.

  You need to identify which process prevents the application from responding.

  What should you do?

  A. Run DBCC INPUTBUFFER.
  B. Query sys.dm_exec_session_wait_stats.
  C. Run sp_autostats.
  D. Run sp_who.
  E. Query sys.dm_db_resource_stats.
  B. Query sys.dm_exec_session_wait_stats.

• Question 127:

  You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system.

  The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups.

  These backups are stored on a backup server in the company's data center. Every week, the company places the full backup on a tape and sends it to a third-party backup storage system. The company is worried that a disaster might occur

  that could destroy their computer center and cause them to lose orders.

  You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.

  What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

  A. Set up SQL Server Always On with a SQL Azure database as a replica.
  B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
  C. Put the differential backup on tape and send it to the third-party backup storage system.
  D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.
  D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.

• Question 128:

  You maintain a Microsoft SQL Server database named DB1. You create a maintenance plan to run the DBCC CHECKDB command regularly to identify possible database corruption. DB1 has the following table:

  

  Filegroup FG1 is read-only. You set FG1 to read-only and create a full backup.

  The maintenance plan does not identify any errors after the backup. However, you observe the following error in the logs.

  Msg 8979, Level 16, State 1, Line 1 Table error: Object ID 885578193, index ID 3, partition ID***, alloc unit ID *** (type In-row data). Page (***) is missing a reference from previous page (***). Possible chain linkage problem.

  You need to resolve the database corruption issues.

  What should you do?

  A. Change FG1 from read-only to read/write
  B. Update the statistics on index ID 3 in Table1
  C. Drop and rebuild index ID 3 in Table1
  D. Restore FG1 from the most recent backup
  E. Backup the tail-logs in DB1
  D. Restore FG1 from the most recent backup

• Question 129:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a database named DB1 that contains a table named Table1.

  You need to audit all updates to Table1.

  Solution: You create an UPDATE trigger on Table1 that inserts modified data into an audit table.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 130:

  You deploy a SQL Server instance named SQLProd that uses SQL Server 2014.

  You need to recommend a solution to monitor the transactions that are running currently against SQLProd.

  The solution must minimize the amount of custom code required.

  What should you recommend?

  A. Statistics
  B. A dynamic management view
  C. A trigger
  D. User-defined views
  B. A dynamic management view