Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You administer a Microsoft SQL Server 2012 instance.
You discover that the SQL Agent Error Log file is rapidly growing in size.
You need to ensure that the SQL Agent Error Log file does not grow rapidly when SQL Server agent jobs execute.
What should you do?
A. Execute the sp_cycle_agent_errorlog stored procedure.
B. Configure event forwarding.
C. Enable the Auto Shrink option on the master database.
D. Enable the Auto Shrink option on the msdb database.
E. Disable the Include execution trace messages feature.
Answer: E
Q2. 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 need to ensure that the minimum amount of data is lost.
Which recovery model should the database use?
A. FULL
B. DBO_ONLY
C. CONTINUE_AFTER_ERROR
D. CHECKSUM
E. NO_CHECKSUM
F. SIMPLE
G. Transaction log
H. SKIP
I. RESTART
J. COPY_ONLY
K. NORECOVERY
L. BULK_LOGGED
M. Differential
N. STANDBY
Answer: L
Q3. You administer a Windows Azure SQL Database database named Orders.
You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q4. You maintain several databases on a 32-bit Microsoft SQL Server 2005 instance on a Windows Server 2008 R2 64-bit server.
You need to migrate the databases to a 64-bit SQL Server 2012 instance on the same server. You also need to ensure that the new Transact-SQL functionality in SQL Server 2012 can be used in the database after the migration.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Perform a side-by-side installation of a 32-bit SQL Server 2012 instance.
B. Perform a side-by-side installation of a 64-bit SQL Server 2012 instance.
C. Perform an in-place upgrade to 64-bit SQL Server 2012.
D. Detach the database from the old instance and attach it to the new instance.
E. Change the compatibility level of the database.
Answer: BDE
Q5. 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
Q6. Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day.
You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime = GETDATE()
C. SELECT COUNT(*) FROM SalesOrders
WHERE CONVERT(VARCHAR, OrderTime, 112) = CONVERT(VARCHAR, GETDATE(I, 112))
D. SELECT COUNT(*) FROM SalesOrders
WHERE OrderTime >= CONVERT(DATE, GETDATE())
AND OrderTime < DATEADD(DAY, CONVERT(DATE, GETDATE()))
Answer: D
Q7. You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database.
The recovery model and backup schedule are configured as shown in the following table:
At 16:20 hours, you discover that pages 17, 137, and 205 on one of the database files are corrupted on the transactional database. You need to ensure that the transactional database is restored. You also need to ensure that data loss is minimal.
What should you do?
A. Perform a partial restore.
B. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
C. Perform a point-in-time restore.
D. Restore the latest full backup.
E. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
F. Perform a page restore.
G. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
H. Restore the latest full backup. Then, restore the latest differential backup.
Answer: F
Q8. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that data changes are sent to a non-SQL Server database server in near real time.
You also need to ensure that data on the primary server is unaffected.
Which configuration should you use?
A. . SQL Server that includes an application database configured to perform transactional replication
B. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
C. . Two servers configured in different data centers
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
. One server configured as an Active Secondary
D. . SQL Server that includes an application database configured to perform snapshot replication
E. . Two servers configured in the same data center
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
. One server configured as an Active Secondary
F. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. . Two servers configured in a Windows Failover Cluster in the same data center
. SQL Server configured as a clustered instance
H. . Two servers configured in the same data center
. A primary server configured to perform log-shipping every 10 minutes
. A backup server configured as a warm standby
Answer: A
Q9. You install a Microsoft SQL Server 2012 instance.
The instance will store data extracted from two databases running on Windows Azure SQL Database.
You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the data.
You need to ensure that the data steward is given the correct client tools to perform these tasks.
Which set of tools should you install?
A. SQL Server Management Studio and Distributed Reply Client
B. Data Quality Client and SQL Server Data Tools
C. Data Quality Client and Distributed Replay Client
D. SQL Server Management Studio and Master Data Services
Answer: B
Q10. You administer a Microsoft SQL Server 2012 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. Processor affinity
B. Lightweight pooling
C. Max worker threads
D. I/O affinity
Answer: D
Q11. You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size.
The Sales database is configured as shown in the following table.
You discover that all files except Sales_2.ndf are corrupt.
You need to recover the corrupted data in the minimum amount of time.
What should you do?
A. Perform a restore from a full backup.
B. Perform a transaction log restore.
C. Perform a file restore.
D. Perform a filegroup restore.
Answer: C
Q12. You administer a single server that contains a Microsoft SQL Server 2012 default instance.
You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.
You need to ensure that the application login is unable to access other production databases.
What should you do?
A. Use the SQL Server default instance and configure an affinity mask.
B. Install a new named SQL Server instance on the server.
C. Use the SQL Server default instance and enable Contained Databases.
D. Install a new default SQL Server instance on the server.
Answer: B
Q13. 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 are truncated.
The following error message is an example of the truncated error message:
"Executed as user CONTOSO\ServiceAccount....0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserveD. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code 0x00000000 Source Log Import Activity Descript... The package execution fA. .. The step failed. "
You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.
What should you do?
A. Expand agent logging to include information from all events.
B. Disable the Limit size of job history log feature.
C. Configure event forwarding.
D. Configure output files.
Answer: D
Q14. You administer a Microsoft SQL Server 2012 database named Orders. Orders contains a table named OrderShip that is defined as follows:
Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?
A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCode IS NOT NULL
C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL
Answer: B
Q15. You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup.
Which backup option should you use?
A. STATS
B. COMPRESSION
C. CHECKSUM
D. IN IT
Answer: A