70-462 Free Practice Test

- (Exam Topic 3)
You use a Microsoft SQL Server 2012 database. You need to create an indexed view within the database for a report that displays Customer Name and the total revenue for that customer.
Which four T-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 5)
You administer a Microsoft SQL Server environment. The environment contains a SQL Server Reporting Services (SSRS) instance.
You need to ensure that users can create subscriptions. What should you configure?

1. A. item security for the reports
2. B. SSRS Execution Account
3. C. processing options for the reports
4. D. SQL Agent Proxy Account

Correct Answer: B

- (Exam Topic 5)
You administer a Microsoft SQL Server database server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to gather blocking data over an extended period of time. You also need to ensure minimum impact to server performance.
What should you do?

1. A. Run the sp_who2 command from a query window and examine the BlkBy column.
2. B. Use the Job Activity monitor to review all processes that are actively runnin
3. C. Review the Job History to find out the duration of each step.
4. D. Use sp_configure to set a value for blocked process threshol
5. E. Create an extended event session.
6. F. Use SQL Profiler to trace all queries that are processing on the serve
7. G. Filter queries that have a Durationvalue of more than 1,000 ms.
8. H. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.

Correct Answer: E
You can track down queries that are causing deadlocks. On solution is using a trace flag to write the deadlock information to the error log. You can implement trace flag 1222 to do just that.
There are two ways to enable global trace flags. You can enable the trace flag when SQL Server starts by using the -T1222 startup option, or you can use the DBCC TRACEON(1222,-1) command after SQL Server has started.
References:
http://www.mssqltips.com/sqlservertip/2130/finding-sql-server-deadlocks-using-trace-flag-1222/ http://msdn.microsoft.com/en-us/library/ms188396.aspx

- (Exam Topic 3)
You administer a Microsoft SQL Server database. The database is in the Simple recovery mode. You schedule the following backup plan:
✑ Full backup every day at midnight
✑ Differential backups every hour on the hour, except at midnight
✑ Transaction log backups every ten minutes, starting 10 minutes from the hour, except on the hour
The database fails at 20:45 hours.
You need to use SQL Server Management Studio (SSMS) to begin restoring the database with a minimum amount of data loss.
Which options should you select on the Options page of the Restore Database window? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Solution:
First, restore full database backup, differential database backup and all transaction log backups WITH NORECOVERY Option. After that, bring back database online using WITH RECOVERY option.
References:
https://blog.sqlauthority.com/2009/07/15/sql-server-restore-sequence-and-understanding-norecovery-and-recove

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 5)
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

1. A. Natural
2. B. Explicit
3. C. Parent
4. D. Recursive

Correct Answer: D