MSCE Interview Questions and Answers

QUESTION 131
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional.
Certkiller .com has recently appointed a new part-time system administrator who is
responsible for coming to work on Fridays. The newly appointed system
administrator has recently implemented SQL Server 2000 which is currently under
evaluation and is not yet used to support mission-critical business operations.
The part-time administrator has configured the SQL Server to notify you when
errors occur in the administrator's absence. On the next Monday you discovered
that you received notifications indicating that the transaction log in the CK_Orders
database is full. You additionally received the same error messages throughout the
day but then you to stop receiving these messages. You want to know why you
stopped receiving the error messages and what the cause could be.
What should you do?

A. To conserve system resources the same error message is displayed only a specific
number of times.
B. The SQL Server configuration was automatically adjusted and resolved the problem.
C. Because of the full transaction log the SQL Server Agent has stopped running.
D. The Windows Application log is full.

Answer: D

Explanation: In the scenario you should always remember that in order to prevent
the transaction log from becoming full that you should frequently backup the log
using the default options or switch to the Simple Recovery model.

Incorrect Answers:

A, B, C: In the scenario you should remember that each sysmessage error of
severity 19 or higher is recorded in the Windows Application log. If the log becomes
will and is not configured to overwrite events then you will stop receiving the error
messages until the existing events are saved or deleted to free space.


QUESTION 132
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. Certkiller .com has its headquarters in Miami and branch offices in
Chicago and Detroit. All servers on the Certkiller .com network run Windows 2000
Server and all client computers run Windows XP Professional.
You are responsible for administering several instance of SQL Server 2000 at the
main office. You have recently received instruction to define 50 alerts in an instance of SQL Server 2000. The alerts you are defining should be defined on all the branch office SQL Server 2000 computers. You are required to achieve the scenario using the least amount of administrative effort.
What should you do?

A. One script should be generated for each of the alerts and run each script on a
destination server.
B. A DTS package should be created to copy the msdb database to each destination
server.
C. A DTS package should be created to copy the master database to each destination
server.
D. One script should be generated for all the alerts and run the script on each destination server.

Answer: D

Explanation:

In the scenario you are required to have the same alerts configured on
all the branch office SQL Server 2000 computers. You achieve the scenario using
the least administrative effort when creating a single script which defines all the
configured alerts.

Incorrect Answers:
A: This option should not be considered in the scenario because you are required to use the least administrative effort and this action does not comply.
B, C: In the scenario you should remember that it is not recommended to copy the
msdb database as the servers are configured with different information. You should also not copy the master database as it holds database startup settings.


QUESTION 133
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional.
You are responsible for administering the SQL Server 2000 database of
Certkiller .com which is used to store business information. You have recently
received instruction from the Certkiller .com network CIO to create a Data
Transformation Services (DTS) package that will be used to export the weekly sales
data from the database into a decision-support system (DSS) database.
You are in the process of configuring this package to be scheduled to run every
Friday at 12:30 A.M. You are required to be notified if the package fails and should
accomplish the task using the least amount of administrative effort.
What should you do?

A. The DTS package should be configured to write an event to the application log upon
completion and configure an alert to notify you.
B. The DTS package should be configured to log its execution in SQL Server and configure an alert to notify you.
C. An Execute Process task should be included in the DTS package that executes when
an error occurs in the package and configure an alert to notify you.
D. An Execute SQL task should be included in the DTS package that executes when an
error occurs in the package and configure an alert to notify you.

Answer: A

Explanation: In the scenario you should always remember when enabling this
option an event will be written to the Application log when the package completes.
You should then configure the alert for this specific event to fire when the error is
logged.

Incorrect Answers:

B, D: This option could be used in the scenario but should not be used as it requires to much administrative effort to implement than simply enabling Write completion status to event log.C: This option should not be used in the scenario because this action requires to much administrative effort to implement and the package execution would record data for each step in the msdb database.


QUESTION 134
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional. Certkiller .com has its
headquarters in Chicago and branch offices in Dallas and Miami. The branch
offices are connected to the main office by 512-Kbps links.
You are responsible for administering multiple SQL Server 2000 database of
Certkiller .com which is used to store business information. Certkiller .com has
recently expressed concerns that the branch office database administrators are not
properly trained. The branch office networks currently have their technical
personnel available to perform general administrative duties.
You have recently received additional instruction from the Certkiller .com network
CEO to monitor each instance of SQL Server from the main office using the least
administrative effort.
What should you do?

A. The branch office local administrators should be instructed to e-mail all SQL Server event messages to you
B. A trigger should be configured on the Application log that will fire each time an event message with a specified severity level is recorded
C. The msdb database should be periodically sent from the branch offices to a SQL
Server at the main office
D. From Enterprise Manager you should connect to all remote instances of SQL Server
and configure events to be forwarded from the branch office servers to a master server at the main office.

Answer: D

Explanation: In the scenario you should keep in mind that using the Enterprise
Manager you are able to connect to each instance of SQL Server and make the
appropriate configuration changes as required. Taking this action in the scenario
does involve the least amount of administrative effort.

Incorrect Answers:

A: This option should not be used in the scenario as there is proper connectivity, this option would require more administrative effort to implement.
B: This option should not be considered in the scenario because you are not able to
configure triggers in the Windows application log.
C: There is no need to take this action in the scenario as you are required to be notified when certain events occurred in the branch offices.


QUESTION 135
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Windows 2000 domain named Certkiller .com. All servers
on the Certkiller .com network run Windows 2000 Server and all client computers
run Windows 2000 Professional.

The Certkiller .com network contains a Microsoft SQL Server 2000 database server
named Certkiller -DB01 which you administer. Certkiller -DB01 hosts a
database named CK_Manufacturing that is responsible for driving the
manufacturing process and monitoring of equipment. The monitoring of equipment
is accomplished through stored procedures.

You are required to ensure that only the on-duty manager is paged if the
temperature of equipment reaches a specific point. You create a trigger on the table
to raise an error by msgid if the value is exceeded. You are required to choose the
additional steps to take.
What should you do? (Choose all that apply)

A. A Windows Performance alert should be created
B. An Event Notification should be created
C. Operators should be created for each manager
D. A SQL Server Agent alert should be created
E. sp_messages should be run to add the error messages to the sys.messages table

Answer: C, D, E

Explanation: You should remember in the scenario that you can use the SQL Server
Agent to configure alerts that page the operator in response to SQL Server object
counter values, errors logged to the Windows event log or the Windows
Management Instrumentation (WMI) events.

Incorrect Answer:
A: This option should not be considered for creation in the scenario because the alert type is not used to respond to errors raised in SQL Server triggers.
B: This option should not be considered for creation in the scenario because the option could be used in the scenario but this option requires too much administration effort.


QUESTION 136
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional.
The network users of Certkiller .com has recently began experiencing difficulties
when they attempt to connect to an instance of SQL Server 2000. The network users
complain that there connections are very slow and are sometimes dropped because
they timeout when the users try and manipulate data in the databases.
You later decide to make use of System Monitor and select he counters below:
1. Processor: % Processor Time 2. Processor: % Privileged Time
3. Processor: % User Time
During the course of the business day you discover that the value of the Processor:
% Processor Time counter is approximately 80 and the value of the Processor: %
Privileged Time counter is usually between 90 and 95. You additionally discover
that the Processor: % User Time counter is usually between 15 and 20. You are
required to prevent the user connections from timing out.
What should you do?

A. You should implement a striped volume to host the databases.
B. You should upgrade to a faster CPU.
C. You should add more disks to the server.
D. You should implement a hardware RAID device to host the databases.

Answer: D

Explanation: In the scenario it should be recommended that we make use of a
hardware RAID solution because this type of solution is generally faster than
software RAID solutions and do not require as much system resources.

Incorrect Answers:

A: This option should not be used in the scenario because there is no indication in the scenario that the current disk is at a premium.
B: This option should not be considered in the scenario because upgrading to a faster
CPU will not solve the performance problems.
C: This option should not be considered in the scenario because there is no indication that the disk is at a premium so adding more disk would not solve the performance problem.


QUESTION 137
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional.
You are responsible for administering the SQL Server 2000 database of
Certkiller .com which is used to store business information. The network users of
Certkiller .com have recently started reporting that the overall performance of the
database is very poor. During your maintenance tasks you open System Monitor
and examine the counters and values below:
1. Memory: Pages/sec 300
2. Physical: % Disk Time 260
3. Processor: % Processor Time 10
You are required to improve the overall performance of the SQL Server computer.
What should you do?

A. You should add additional RAM.
B. You should increase the virtual memory.
C. You should upgrade the physical disk.
D. You should upgrade the CPU.

Answer: A

Explanation: In the scenario you should definitely consider adding additional RAM
to the SQL Server computer because the System Monitor output indicates that the
server requires more RAM to alleviate the performance problems in the scenario.

Incorrect Answers:
B: You should not do this in the scenario because the current output indicates that a
large amount of paging takes place as a result of having to little RAM, increasing the value will not improve performance.
C: You should first determine how many disk drives the server has in order to properly conclude that the disk subsystem requires upgrading.
D: This option should not be used in the scenario because the counter value is
acceptable, the amount of RAM in the SQL Server is inefficient, and this raises the disk time activity as paging takes place.


QUESTION 138
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional. You are responsible for
administering the SQL Server 2000 database of Certkiller .com which is used to store
business information.

Certkiller .com network users make use of a custom application to connect to the
SQL Server. Certkiller .com has recently decided to change their business practice
policies. The new business practice requires that the custom application be changes
since the application is based on stored procedures which have been changed to
comply with the new business practice. Certkiller .com has authorized some
employees to access the database directly whilst the other users use the custom
application. The authorized employees make use of Query Analyzer to execute
queries.
The Certkiller .com network users recently started reporting that the application is
much slower. During the course of the day you decide to use System Monitor and
select several counters and discover the following values:
1. PhysicalDisk: Avg. Disk Queue Length has a value of 1
2. SQLServer: BufferManager Buffer cache hit ratio has a value of 99
3. SQLServer:Memory Manager Lock Blocks in context of the database has a value
of 15
4. SQLServer:Cache Manager: Cache Hit Ratio of Adhoc Sql Plans has a consistent
value above 95
5. SQLServer:Cache Manager: Cache Hit Ratio of Procedure Plans instance has a
value of 1
You are required to improve the performance of the custom application for the
network users of Certkiller .com.

What should you do?

A. The WITH RECOMILE option should be removed from the definition of the stored
procedure that the application uses.
B. All application that access the database should be configured to operate implicit
transactions mode.
C. You should add an additional disk drive.
D. You should add an additional CPU.

Answer: A

Explanation: In the scenario you should always remember because the Procedure
Plans has a low value the SQL Server is ignoring the cached execution plans when
executing stored procedures because of the WITH RECOMPILE option in the
definition of the stored procedure.

Incorrect Answers:
B: This option should not be considered in the scenario as it will deteriorate the
performance by holding locks for longer periods of time.
C: This action should not be taken in the scenario, the only time to add an additional disk
drive would be if the counter value is greater than 2.
D: This option should not be used in the scenario as there was no data captured for the Processor counters and you cannot accurately access the need for an additional CPU.


QUESTION 139
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000 Server
and all client computers run Windows XP Professional.
You are responsible for administering the SQL Server 2000 database of
Certkiller .com which is used to store business information. The network users of
Certkiller .com have recently started reporting that the overall performance of the
database is very poor. During your maintenance tasks you open System Monitor
and add several counters for review below:
1. Memory: Pages/sec
2. PhysicalDisk: Average Queue Length
3. Processor: % Processor Time
During the course of the week you review the System monitor results and discover
that the Processor: % Processor Time counter is above 95, Memory: Pages/sec has a
value that is usually low and at times 20. The PhysicalDisk: Average Queue Length
counter has a value between 1 and 2. You are required to improve the performance
of the server for the Certkiller .com network users.

What should you do?

A. You should increase the size of the swap file.
B. You should add additional RAM.
C. You should add an additional hard disk.
D. You should add another CPU to the server.

Answer: D

Explanation: You should consider adding an additional CPU to the server in
question because a typical performance for a server handling non-idle threads
should have a counter value of 80-85% and in the scenario the counter value is
constantly above 95.

Incorrect Answers:

A: This option should not be used in the scenario as there is no indication that the
Windows swap file is configured to small.
B: This option should not be used in the scenario as the problem is not with the
RAM, the Processor: % Processor Time counter value should be operating between 80
-85% for non-idle threads and in the scenario the value is above 95.
C: This option should not be considered in the scenario as there is no indication that an additional disk drive is required. This option should only be used if the counter value is greater than 2.


QUESTION 140
You work as the network database administrator at Certkiller .com. The
Certkiller .com network consists of a single Active Directory domain named
Certkiller .com. All servers on the Certkiller .com network run Windows 2000
Advanced Server and all client computers run Windows 2000 Professional,
Windows NT Workstation, Windows ME, Windows XP Professional or Windows
9x.

You are responsible for administering the SQL Server 2000 database of
Certkiller .com which is used to store business information. The SQL Server 2000
computer has eight CPUs and 2 GB of RAM. The SQL server also has two NICs
which are used to connect to different subnets. The network users of Certkiller .com
have recently complained about the server performing slow in the morning.
During the course of the day you use System Monitor to monitor server activity in
the morning using the counters below and discover the following values:
1. Processor: % Processor Time has a value of 80
2. Processor: % Privileged Time has a value of 25
3. Processor: % User Time has a value of 85
4. Memory: Available Mbytes has a value of 950
You are required to improve the performance of the server for the Certkiller .com
network users.

What should you do?

A. You should assign more memory to the SQL Server.
B. You should add additional RAM to the server.
C. You should configure the SQL Server to run on CPUs 4, 5, 6 and 7 only.
D. You should set the affinity mask to 63.

Answer: D

Explanation: In the scenario the SQL Server also has to authenticate domain
logons, by configuring the affinity mask of 63 you enable SQL Server to use the first
6 CPUs allowing the remaining processors to be dedicated to the NICs.

Incorrect Answers:

A: In the scenario you should not make use of this option because the SQL Server is
capable of dynamically adjusting its memory usage based on the physical memory
available.
B: This option should not be considered in the scenario because the System Monitor
indicates that memory available is sufficient.
C: This option should not be used in the scenario because the operating system
automatically assigns deferred process calls associated with the NICs to the
processors with the highest numbers processor 6 and 7.