Question 1) Microsoft SQL Server 2005

Posted on
Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

Objective: Supporting Data Consumers
SubObjective: Managing XML Data

Single Answer Multiple Choice

You are a database administrator managing all the SQL Server 2005 databases of your company. You want to import the XML data from a text file to a database named Prod that is operating in the Full Recovery model. You copy the data onto a temporary table in the database and then transfer this data to the other tables in the database.

The text files storing the data are large in size, and you want to prevent the transaction log from becoming full during the data import process.

What should you do?

A. Disable the firing of triggers during the import process.
B. Disable the copying of indexes during the import process.
C. Perform parallel import of data from multiple clients to the temporary table.
D. Alter the database to use the Bulk-Logged Recovery model.

Answer:
D. Alter the database to use the Bulk-Logged Recovery model.

Tutorial:
The option stating that you should alter the database to use the Bulk-Logged Recovery model is correct because the Bulk-Logged Recovery model logs minimum data into the transaction logs in a bulk-import operation. This prevents the transaction log from becoming full. This is because very less data is logged to the transaction log. In this scenario, the database is presently operating in the Full Recovery model. The Full Recovery model will fully log the inserted rows, causing the transaction log to become full. To minimize this log information, you should alter the database to use the Bulk-Logged Recovery model and then switch over to the Full Recovery model after the bulk import process is over.

The option stating that you should disable the firing of triggers during the import process is incorrect because the firing of triggers does not affect the generation of logs in the import process. Disabling triggers optimizes the performance of the process by reducing the time required to complete the import.

The option stating that you should disable the copying of indexes during the import process is incorrect because you cannot disable the copying of indexes during the import process. To enable minimal logging, you can drop the indexes that are not required from the table. If no indexes exist on the table, the data pages are minimally logged. If the table has a clustered index, the index pages and the data pages are fully logged even if the database is operating in the Bulk-Logged Recovery model.

The option stating that you should perform parallel import of data from multiple clients to the temporary table is incorrect because importing data from multiple clients does not affect the logs generated in the process. Importing data can improve the performance of the import process, and decrease the time required for the import process to be completed.


Reference:
MSDN, Search, “Enterprise Servers and Development,” “SQL Server,” “SQL Server 2005 Documentation,” “SQL Server 2005 Books Online,” “SQL Server Database Engine,” “SQL Server Database Engine,” “Administering the Database Engine,” “Importing and Exporting Bulk Data,” “Optimizing Bulk Import Performance.”

MSDN, Search, “Enterprise Servers and Development,” “SQL Server,” “SQL Server 2005 Documentation,” “SQL Server 2005 Books Online,” “SQL Server Database Engine,” “SQL Server Database Engine,” “Administering the Database Engine,” “Importing and Exporting Bulk Data,” “Optimizing Bulk Import Performance,” “Prerequisites for Minimal Logging in Bulk Import.”

These questions are derived from the Self Test Software Practice Test for Microsoft exam 70-431-TS: Microsoft SQL Server 2005 Implementation and Maintenance

Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>