How to pass Microsoft 70-462 Real Exam in 24 Hours [practice test 16-30]

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-462 Exam Dumps (PDF & VCE):
Available on: https://www.certleader.com/70-462-dumps.html


70-462 Product Description:
Exam Number/Code: 70-462 vce
Exam name: Administering Microsoft SQL Server 2012 Databases
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Instant Access to Free VCE Files: Microsoft 70-462 Administering Microsoft SQL Server 2012 Databases

70-462 examcollection

Precise of 70-462 exam guide materials and faq for Microsoft certification for IT learners, Real Success Guaranteed with Updated 70-462 pdf dumps vce Materials. 100% PASS Administering Microsoft SQL Server 2012 Databases exam Today!

2016 Apr 70-462 Study Guide Questions:

Q16. You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views. 

You grant User1 access to execute the stored procedure. 

You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. 

What should you do? (Each correct answer presents a complete solution. Choose all that apply.) 

A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role. 

B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure. 

C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement. 

D. Grant the db_owner role on the database to User1. 

E. Grant the sysadmin role on the database to User1. 

Answer: BC 


Q17. You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistoncal. 

You write the following Transact-SQL query: 

INSERT INTO OrdersHistorical 

SELECT * FROM CompletedOrders 

You need to optimize transaction logging and locking for the statement. Which table hint should you use? 

A. HOLDLOCK 

B. ROWLOCK 

C. XLOCK 

D. UPDLOCK 

E. TABLOCK 

Answer: E 


Q18. You administer a Microsoft SQL Server 2012 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 

Answer: A 


Q19. You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the Windows NT Service virtual accounts for all SQL Services. 

You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as 

a pull subscriber. 

The distribution agent on ProdSrv2 regularly fails, displaying the following error message: 

"Cannot access the file. Operating system error code 5 (Access is denied.)." 

You need to configure the distribution agent by granting only the minimum required access to all accounts. 

What should you do? 

A. Configure the Subscriber to use the Local System account. 

B. Configure the SQL Server Agent service to run under the Local System account. 

Configure the Subscriber to use the SQL Server Agent service account. 

C. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1. 

D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1. 

Answer: D 


Q20. You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. 

The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do? 


A. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement. 

B. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view. 

C. Create an AFTER UPDATE trigger on the view. 

D. Create an INSTEAD OF UPDATE trigger on the view. 

Answer: D


70-462 free practice exam

Up to the minute 70-462 free download:

Q21. You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster. 

An additional node named Node C has been added to the existing cluster. 

You need to ensure that the SQL Server instance can use all nodes of the cluster. 

What should you do? 

A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A. 

B. Use Node A to install SQL Server on Node C. 

C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. 

D. Use Cluster Administrator to add a new Resource Group to Node B. 

Answer: C 


Q22. You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured. 

When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail. 

The following error message is an example error message: 

"The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1 (Subplan_1)." 

You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained. 

What should you do? 

A. Configure output files. 

B. Expand agent logging to include information from all events. 

C. Disable the Limit size of job history log feature. 

D. Configure event forwarding. 

Answer: B 


Q23. You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction. 

You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index. 

You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process. 

Which Transact-SQL batch should you use? 

A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE 

B. ALTER INDEX ALL ON AccountTransaction REBUILD 

C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD 

D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING 

Answer: A 


Q24. You administer a Microsoft SQL Server 2012 database. 

Users report that an application that accesses the database displays an error, but the error does not provide meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error. 

You need to identify the root cause of the issue by retrieving the error message. 

What should you do? 

A. Create an Extended Events session by using the sqlserver.error_reported event. 

B. Create a SQL Profiler session to capture all ErrorLog and EventLog events. 

C. Flag all stored procedures for recompilation by using sp_recompile. 

D. Execute sp_who. 

Answer: A 


Q25. You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. 

You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements: 

. Does not return an error 

. Closes all opened transactions 

Which Transact-SQL statement should you use? 

A. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ TRANCOUNT = 0 ROLLBACK TRANSACTION; END CATCH 

B. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; THROW; END CATCH 

C. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT = 0 ROLLBACK TRANSACTION; THROW; END CATCH 

D. BEGIN TRANSACTION BEGIN TRY EXEC dbo.ModifyData COMMIT TRANSACTION END TRY BEGIN CATCH IF @@ERROR != 0 ROLLBACK TRANSACTION; END CATCH 

Answer: D 


70-462 exam

Refined 70-462 testing engine:

Q26. You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. 

You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. 

What should you do? 

A. Query the sys.dm_db_index_usage_stats DMV. 

B. Query the sys.dm_db_missing_index_details DMV. 

C. Use the Database Engine Tuning Advisor. 

D. Query the sys.dm_db_missing_index_columns DMV. 

Answer: C 


Q27. You administer a Microsoft SQL Server 2012 database. 

You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated permissions. 

You need to ensure that the job step can run using a different user account. 

What should you use? 

A. A notification 

B. A schedule 

C. A job category 

D. A proxy 

Answer: D 


Q28. You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. 

You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales. 

What should you create? 

A. An Alert 

B. A Resource Pool 

C. An Extended Event session 

D. A Server Audit Specification 

E. A SQL Profiler Trace 

F. A Database Audit Specification 

G. A Policy 

H. A Data Collector Set 

Answer: F 


Q29. You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). 

The financial database has the following characteristics: 

. A data file of 2 terabytes is located on a dedicated LUN (drive D). 

. A transaction log of 10 GB is located on a dedicated LUN (drive E). 

. Drive D has 1 terabyte of free disk space. . Drive E has 5 GB of free disk space. 

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. 

Five percent of the existing data is modified each day. 

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. 

Each data load adds 3 GB of data to the database. 

These data load operations must occur in the minimum amount of time. 

A full database backup is performed every Sunday at 10:00 hours. 

Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. 

You implement log shipping of the financial database to another SQL Server 2012 instance. 

You decide to failover to this secondary database. 

You need to ensure that all transactions will be replicated to the secondary database. 

Which backup option should you use? 

A. Differential 

B. Transaction Log 

C. FULL 

D. SIMPLE 

E. SKIP 

F. RESTART 

G. STANDBY 

H. CHECKSUM 

I. DBO_ONLY 

J. COPY_ONLY 

K. NORECOVERY 

L. NO_CHECKSUM 

M. CONTINUE_AFTER_ERROR 

N. BULK_LOGGED 

Answer: K 


Q30. You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster. 

An additional node named Node C has been added to the existing cluster. 

You need to ensure that the SQL Server instance can use all nodes of the cluster. 

What should you do? 

A. Run the New SQL Server stand-alone installation Wizard on Node C. 

B. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. 

C. Use Node B to install SQL Server on Node C. 

D. Use Node A to install SQL Server on Node C. 

Answer: B 



see more Administering Microsoft SQL Server 2012 Databases