Administering Microsoft SQL Server 2012 Databases v13.0

Page:    1 / 17   
Total 248 questions Expand All

You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?

  • A. Execute sp_configure 'max log size', 2G.
  • B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.
  • C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
  • D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.


Answer : D

You administer a Microsoft SQL Server database that is used by an application.
Users of the application report performance issues.
You need to choose the appropriate tool for performance-tuning of SQL Server databases.
Which tool or tools should you use? (To answer, drag the appropriate tool or tools to their
corresponding task or tasks in the answer area. Each tool 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.)




Answer :

Explanation:

References: http://msdn.microsoft.com/en-us/library/bb630282.aspx http://msdn.microsoft.com/en-us/library/ms191246.aspx http://msdn.microsoft.com/en-us/library/ms181091.aspx

You administer a Microsoft SQL Server 2012 database.
You use an OrderDetail table that has the following definition:


You need to create a non-clustered index on the SalesOrderID column in the OrderDetail
table to include only rows that contain a value in the SpecialOfferID column.
Which four Transact-SQL statements should you use? (To answer, move the appropriate
statements from the list of statements to the answer area and arrange them in the correct
order.)



Answer :

Explanation:

According to these references, this answer looks correct. References: http://msdn.microsoft.com/en-us/library/ms188783.aspx http://msdn.microsoft.com/en-us/library/ms189280.aspx

You administer a Microsoft SQL Server 2012 database named ContosoDB. The database

  • A. GRANT DELETE ON Purchases. Suppliers TC ContosoUser
  • B. CREATE PROCEDURE Purchases.PurgelnactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
  • C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser
  • D. CREATE PROCEDURE Purchases. PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases. PurgeInactiveSuppliers TO ContosoUser


Answer : B

Explanation: References: http://msdn.microsoft.com/en-us/library/ms188354.aspx http://msdn.microsoft.com/en-us/library/ms187926.aspx

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

Reference: http://msdn.microsoft.com/en-us/library/bb630282.aspx Event sqlserver.error_reported: This event gets fired every time that an error happens in the server

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

  • 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

You administer a Microsoft SQL Server 2012 database.
The database contains a table that has the following definition:


You want to export data from the table to a flat file by using the SQL Server Import and
Export Wizard.
You need to ensure that the following requirements are met:
? The first row of the file contains the first row of data.
? Each record is of the same length.
? The date follows the U.S. date format.
? The file supports international characters.
What should you do? (To answer, configure the appropriate option or options in the dialog
box in the answer area.)



Answer :

Explanation:

References: http://msdn.microsoft.com/en-us/library/ms178804.aspx http://msdn.microsoft.com/en-us/library/ms187828.aspx

You administer a Microsoft SQL Server 2012 server that has multiple databases.
You need to ensure that users are unable to create stored procedures that begin with sp_.
Which three 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 :

Explanation: ? Create a Condition named StoredProcNamingConvention by using the Stored Procedure facet that has a single expression. Set the Field to @Name, Operator to LIKE, and Value to 'sp[_]%'. ? Create a Policy named StoredProcNamingPolicy Set the Check condition to StoredProcNamingConvention and Evaluation Mode to On Change: Prevent ? Enable StoredProcNamingPolicy

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 14:00 hours, you discover that pages 71, 520, and 713 on one of the database files are
corrupted on the reporting database.
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. Restore the latest full backup.
  • D. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  • E. Perform a page restore.
  • F. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  • G. Perform a point-in-time restore.
  • H. Restore the latest full backup. Then, restore the latest differential backup.


Answer : H

Explanation: Restores a file or filegroup in a multi-filegroup database. Note that under the simple recovery model, the file must belong to a read-only filegroup. After a full file restore, a differential file backup can be restored. Page restore Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. Piecemeal restore Restores the database in stages, beginning with the primary filegroup and one or more secondary filegroups. A piecemeal restore begins with a RESTORE DATABASE using the PARTIAL option and specifying one or more secondary filegroups to be restored.

You administer a Microsoft SQL Server 2012 server.
When transaction logs grow, SQL Server must send an email message to the database
administrators.
You need to configure SQL Server to send the email messages.
What should you configure?

  • A. SQL Mail
  • B. An Extended Events session
  • C. Alerts and operators in SQL Server Agent
  • D. Policies under Policy-Based Management


Answer : C

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales
schema.
Some users must be prevented from deleting records in any of the tables in the Sales
schema.
You need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort.
What should you do?

  • A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
  • B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
  • C. Deny Delete permissions on each table in the Sales schema for each user.
  • D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.


Answer : A

You administer two instances of Microsoft SQL Server 2012. You deploy an application that
uses a database on the named instance.
The application is unable to connect to the database on the named instance.
You need to ensure that the application can connect to the named instance.
What should you do?

  • A. Use the Data Quality Client to configure the application.
  • B. Start the SQL Server Browser Service.
  • C. Use the Master Data Services Configuration Manager to configure the application.
  • D. Start the SQL Server Integration Services Service.


Answer : B

You administer a Microsoft SQL Server 2012 database.
You create an availability group named haContosoDbs. Your primary replica is available at
Server01\Contoso01.
You need to configure the availability group to have the highest availability.
You also need to ensure that no data is lost.
Which Transact-SQL statement should you use?


  • A. Option A
  • B. Option B
  • C. Option C
  • D. Option D


Answer : C

You administer three Microsoft SQL Server 2008 R2 instances.
Database mirroring is configured in High-Safety mode with Automatic Failover between the
following three servers:
? SQL1 is the Principal server.
? SQL2 is the mirror server.
? SQL3 is the witness server.
You need to upgrade SQL1 and SQL2 to SQL Server 2012. You need to ensure that
downtime is minimized during the upgrade.
Which six 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 :

You administer several Microsoft SQL Server 2012 servers. Your company has a number
of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for each scenario.
What should you do? (To answer, drag the appropriate replication method or methods to
the correct location or locations in the answer area. Each replication method may be used
once, more than once, or not at all.)




Answer :

Page:    1 / 17   
Total 248 questions Expand All

Talk to us!


Have any questions or issues ? Please dont hesitate to contact us