QUESTION 51
Drag and Drop Question
You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database. When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four 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.)
 
Answer:
 

Continue reading →

QUESTION 41
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server. What should you create?

A.    an Extended Event session
B.    a Policy
C.    a Database Audit Specification
D.    an Alert
E.    a Server Audit Specification
F.    a SQL Profiler Trace
G.    a Resource Pool

Answer: F

Continue reading →

QUESTION 31
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?

A.    CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B.    ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C.    ALTER INDEX ALL ON OrderDetail REBUILD
D.    ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms188388.aspx

Continue reading →

QUESTION 21
You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:
 
You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

A.    Convert all indexes to Column Store indexes.
B.    Implement Unicode Compression.
C.    Implement row-level compression.
D.    Implement page-level compression.

Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/cc280449.aspx http://msdn.microsoft.com/en-us/library/cc280464.aspx http://msdn.microsoft.com/en-us/library/cc280576.aspx http://msdn.microsoft.com/en-us/library/ee240835.aspx

Continue reading →