Latest MCTS 70-432 Dumps Real Exam Download 141-150

Ensurepass

QUESTION 141

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

You have to perform the backup of the default trace according to the requirement of your failure recovery

plan. The default trace is contained in the subdirectory. You have to backup the subdirectory.

Which SQL Server database engine subdirectory should be backed up?

 

A. DATA

B. INSTALL

C. LOG

D. BINN

 

Correct Answer: C

Explanation/Reference: Use the default trace enabled option to enable or disable the default trace log files. The default trace functionality provides a rich, persistent log of activity and changes primarily related to the configuration options. Purpose Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur. Viewing The default trace logs can be opened and examined by SQL Server Profiler or queried with Transact-SQL by using the fn_trace_gettable system function. SQL Server Profiler can open the default trace log files just as it does normal trace output files. The default trace log is stored by default in the MSSQLLOG directory using a rollover trace file. The base file name for the default trace log file is log.trc. In a typical installation of SQL Server, the default trace is enabled and thus becomes TraceID 1. If enabled after installation and after creating other traces, the TraceID can become a larger number.

 

 

QUESTION 142

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a management data warehouse that uses the data collector to collect performance data. You

intend to maintain the management data. You want to implement a data collection process. The process

can gather and upload data in the management data warehouse on different schedules.

What process should you implement?

 

A. You should create a cached data collection

B. You should create an on-demand non-cached data collection

C. You should create a scheduled non-cached data collection

D. You should create two different SQL Agent jobs that are scheduled stimultaneously

 

Correct Answer: A

Explanation/Reference:

Data collection and upload

Specifies how data is collected and uploaded to the management data warehouse. Pick one of the

following options.

Cached. Collection and data upload not on the same schedule.

 

 

QUESTION 143

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a remote SQL Server 2008 instance.

You notice that the application has a poor performance. On the remote SQL Server instance, you capture

a workload of the remote instance to a trace table by using SQL Profiler. Now you have to use the

Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local

SQL Server instance.

So what action should you perform to achieve this goal?

 

A. You should recapture the workload by using the data collector.

B. You should enable the XP_MSVER stored procedure on the local server.

C. You should enable the XP_MSVER stored procedure on the remote server

D. You should recapture the workload to a trace file by using SQL Profiler.

 

Correct Answer: D

Explanation/Reference:

In order to use the Database Engine Tuning Advisor, you need one trace file, here in the question, there is a trace table availlable, which is not good. We have then to restart one trace to a file.

 

 

QUESTION 144

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance which contains a database.

According to the company requirement, you have to move the application from Microsoft SQL Server 2000

to Microsoft SQL Server 2008. You have to monitor the SQL Server instance to record the

use of features.

These features will be discontinued. What should you do?

 

A. The SQL Server 2008 Upgrade Advisor should be used.

B. The SQL Server Profiler which captures the SQL:BatchCompleted and Exception event classes should be used.

C. A SQL server-side trace that captures the Deprecation Announcement and Deprecation Final Support event classes should be used

D. A SQL server-side trace that captures the SQL:BatchCompleted and Exception event classes should be used.

 

Correct Answer: C

Explanation/Reference:

The Deprecation Final Support event class occurs when you use a feature that will be removed from the

next major release of SQL Server. For greatest longevity of your applications, do not use features that

cause the Deprecation Final Support event class or the Deprecation Announcement event class.

Modify applications that use final deprecation features as soon as possible.

 

 

QUESTION 145

You work in a company which is named Wiikigo Corp. The company uses SQL Server 2008. You are the

administrator of the company database.

Now you are in charge of a SQL Server 2008 instance. There is a database named DB1 in the instance.

DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G:

volume; the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP)

data stored in the F: volume.

The backup strategy of DB1 has three requirements:

first, the History filegroup should be backed up at the end of each working day;

second, the PRIMARY filegroup should be backed up five times a day;

third, the transaction log should be backed up every quarter.

You get a report at 13:00 saying that the volume which contains the History filegroup fails.

As the technical support, you have to restore the database, and you must reduce the data loss to the least

as soon as possible.

What should you do?

 

A. You should restore the most recent backup of the History filegroup.

B. You should restore the most recent backup of the History filegroup. Restore all the transaction log backups after the most recent backup of the History filegroup

C. You should backup the transaction log. Then restore the most recent backup of the PRIMARY filegroup and restore the most recent backup of the History filegroup. After the most recent backup of the History filegroup, restore all the transaction log backups

D. You should backup the transaction log. Then restore the most recent backup of the History filegroup and restore all the transaction log backups after the most recent backup of the History filegroup.

 

Correct Answer: D

Explanation/Reference:

As you have made one filegroup backup of the History filegroup, you can restore it and apply all the

transaction log to this restored filegroup.

Or, you need to reduce the the data lost to the least. In this case, you have to first backup the transaction

log and then make the restore.

 

 

QUESTION 146

You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance which contains a database. Now your company CIO assigns a task to you. You have to check the performance of the SQL Server 2008 instance. You have to find out which common language runtime (CLR) queries takes the longest-running time. So in the options below, which dynamic management view should you choose to use?

 

A. sys.dm_os_wait_stats

B. sys.dm_exec_requests

C. sys.dm_exec_sessions

D. sys.dm_exec_query_stats

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 147

You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. The instance has databases for a finance application and a manufacturing application. You want to put some limitation on the maximum CPU query time allowed by the applications, so you configure the Resource Governor to achieve this. But you notice that certain reports do not execute successfully any longer on the manufacturing application. You have to allow the manufacturing application to consume more CPU time, so you have to change the Resource Governor configuration. Which Resource Governor component should you configure?

 

A. The workload group which is used by default

B. The workload group which is used by the finance application

C. The classifier function which identifies the finance application

D. The workload group which is used by the manufacturing application

 

Correct Answer: D

Explanation/Reference:

 

 

QUESTION 148

You work in a company which uses SQL Server 2008. You are the administrator of the company database. Now you are in charge of a SQL Server 2008 instance. For some reason, now the company wants to view the number of users who access the SQL Server instance. The company CIO assigns this task to you. You have to use Windows System Monitor to find out the concrete number. Which performance object should you capture?

 

A. SQLServer:Buffer Manager

B. SQLServer:Access Methods

C. SQLServer:General Statistics

D. SQLServer:Exec Statistics

 

Correct Answer: C

Explanation/Reference:

 

 

QUESTION 149

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named Dworks in the instance. The Dworks database has a table named Bills which

contains several indexes and a great amount of rows. An enterprise Web environment is supported by the

database. On the Bills table 28% of the indexes have been fragmented.

You have to defragment the indexes, making sure that you reduce the effect on database availability to the

least.

In the ALTER INDEX statement, which option should be included?

 

A. Sort in tempdb

B. Online

C. Fill factor

D. Pad index

 

Correct Answer: B

Explanation/Reference:

ONLINE = { ON | OFF }

Specifies whether underlying tables and associated indexes are available for queries and data modification

during the index operation. The default is OFF.

For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to ON an error

is raised.

Note:

Online index operations are available only in SQL Server Enterprise. ON Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table. OFF Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements. Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions: Disabled indexes XML indexes Indexes on local temp tables Partitioned indexes Clustered indexes if the underlying table contains LOB data types Nonclustered indexes that are defined with LOB data type columns Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns.

 

 

QUESTION 150

You work in a company which uses SQL Server 2008. You are the administrator of the company database.

Now you are in charge of a SQL Server 2008 instance.

There is a database named named Dworks in the instance. The table below shows the two tables of the

Dworks database:

There are two partitions in the BillHistory table. Partition1 is empty while partition2 contains all data.

Now you intend to move data from partition2 to the appropriate partition of the BillHistory table. Partions2

contains data for the oldest month of the Bills table.

You are going to develop a process toachieve this.

You must make sure that at the end of every month the process can be repeated.

 

A. 1. Alter the partition function of the BillHistory table by using the split option.

2.

 Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.

3.

 Alter the BillHistory table by using the split option.

B. 1. Alter the partition function of the Bills table by using the split option.

2. Alter the Billstable by using the merge option.

C. 1. Alter the partition function of the BillHistory table by using the split option.

2.

 Switch the appropriate partition of the Bills table to the appropriate partition of the BillHistory table.

3.

 Alter the BillHistory table by using the merge option.

4.

 Alter the Bills table by using the merge option.

D. 1. Alter the partition function of the Bills table by using the split option.

2.

 Alter the BillHistory table by using the split option.

3.

 Alter the partition function of the BillHistory table by using the merge option.

4.

 Alter the Bill stable by using the merge option.

 

Correct Answer: C

Explanation/Reference:

1.

 split the boundaries of the partition function of the target table

2.

 switch the appropriate partition of source table to the target table

3.

 merge the boundaries of the partition function of the target table

4.

 merge the boundaries of the partition function of the source table

Theory

Alters a partition function by splitting or merging its boundary values. By executing ALTER PARTITION FUNCTION, one partition of any table or index that uses the partition function can be split into two partitions, or two partitions can be merged into one less partition. ALTER PARTITION FUNCTION partition_function_name(){ SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value )} [ ; ] SPLIT RANGE ( boundary_value ) Adds one partition to the partition function. boundary_value determines the range of the new partition, and must differ from the existing boundary ranges of the partition function. Based on boundary_value, the Database Engine splits one of the existing ranges into two. Of these two, the one where the new boundary_value resides is considered the new partition. Important: A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition. A filegroup that already holds partitions can be designated to hold additional partitions. Because a partition function can participate in more than one partition scheme, all the partition schemes that use the partition function to which you are adding partitions must have a NEXT USED filegroup. Otherwise, ALTER PARTITION FUNCTION fails with an error that displays the partition scheme or schemes that lack a NEXT USED filegroup. MERGE [ RANGE ( boundary_value) ] Drops a partition and merges any values that exist in the partition into one of the remaining partitions. RANGE (boundary_value) must be an existing boundary value, into which the values from the dropped partition are merged. The filegroup that originally held boundary_value is removed from the partition scheme unless it is used by a remaining partition, or is marked with the NEXT USED property. The merged partition resides in the filegroup that originally did not hold boundary_value. boundary_value is a constant expression that can reference variables (including user-defined type variables) or functions (including userdefined functions). It cannot reference a Transact-SQL expression. boundary_value must either match or be implicitly convertible to the data type of its corresponding partitioning column, and cannot be truncated during implicit conversion in a way that the size and scale of the value does not match that of its corresponding input_parameter_type. SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ] Switches a block of data in one of the following ways: Reassigns all data of a table as a partition to an already-existing partitioned table. Switches a partition from one partitioned table to another. Reassigns all data in one partition of a partitioned table to an existing non-partitioned table. If table is a partitioned table, source_partition_number_expression must be specified. If target_table is partitioned, target_partition_number_expression must be specified. If reassigning a table’s data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty. If reassigning one partition’s data to form a single table, the target table must already be created and it must be empty. Both the source table or partition, and the target table or partition, must reside in the same filegroup. The corresponding indexes, or index partitions, must also reside in the same filegroup. Many additional restrictions apply to switching partitions. For more information, see Transferring Data Efficiently by Using Partition Switching. table and target_table cannot be the same. target_table can be a multi-part

identifier.

source_partition_number_expression and target_partition_number_expression are constant expressions

that can reference variables and functions. These include user-defined type variables and user-defined

functions. They cannot reference Transact-SQL expressions.

Note:

You cannot use the SWITCH statement on replicated tables.

 

Download Latest MCTS 70-432 Real Free Tests , help you to pass exam 100%.