MSCE Interview Questions and Answers

Your instructions are to ensure that the data is transferred successfully without
incurring extra administrative cost. To this end you need to provide a solution that
will alleviate the problem temporarily.
What should you do?

A. The active portion of the transaction log must be truncated.

B. Another data file should be created in the new filegroup on the disk.

C. File growth should be restricted to 4 GB.

D. File growth should be restricted to 2,500 MB.

Answer: C

Explanation: It is clearly indicated in the exhibit that 3.10 BG free disk space exists on the disk. The fact that the transfers did not occur is probably due to a lack of space because the data file probably reached its maximum size and thus cannot
grow anymore. This means that you should increase data file size to accommodate
regular data transfers temporarily. It is also mentioned in the question that
approximately 50 MB of new data is transferred at a time. Thus increasing the data
file's maximum size by 1 GB should provide enough space to complete at least 20
more data transfers. Thereafter you will be compelled to add another disk and
create another data file for the database. But for the purposes of this question you
should restrict the file growth to 4 GB to alleviate the problem temporarily.

Incorrect answers:

A: The active portion of a transaction log cannot be truncated because it is associated with change records that are associated with uncommitted transactions and must be available in case of recovery of the database.
B: Although you could create another data file in a new filegroup on the same disk,
you would not be providing extra space, unless you moved some of the existing
database objects to the additional disk space. This thus means administrative extra
cost because then you would also have to monitor space usage in two files instead of onebecause SQL Server does not automatically balance space usage between files that
belong to separate filegroups.
D: You cannot restrict the data file maximum size to 2,500 MB because the file already contains approximately 3 GB of data as shown in the exhibit.

QUESTION 18

You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows Server 2000 and all database servers run SQL
Server 2000. The Certkiller .com network contains a SQL Server 2000 database
server named Certkiller -DB01 that runs on a Windows 2000 Server computer.
Certkiller -DB01 hosts a database named CK_Orders. The CK_Orders properties
are illustrated in the exhibit below:
Exhibit:



Approximately 2 GB of data was loaded into the database after it was created, and
every other business data approximately 120 MB of new data is added. Three weeks
after CK_Orders was created and in operation, you attempt to add new data.
However, the database is very sluggish, and the disk was thrashing. On the
following Monday you added new data to CK_Orders and did not experience any
problems. The Wednesday, when attempting to ass new data to CK_Orders, you
again find that the database is sluggish and the disk is thrashing.
You do not want the periodic performance degradation and thus investigate the
issue. You found that the disk contains 30 GB of free space. You now need to decide
on a plan of action to alleviate the problem.

What should you do?

A. The transaction log should be truncated.

B. The sp_spaceused stored procedure should be executed.

C. CK_Orders should be backed-up using the NO_TRUNCATE option.

D. The current size of the file and the growth increment should be increased.

Answer: D

Explanation: Gauging by what is mentioned in the question; you failed to accurately
assess the amount of space that CK_Orders would use. This resulted in the periodic
performance deterioration every time new data is added when the data file is full.
When creating a database it is recommended to configure the file and growth
increments to accommodate the database. In this case you need to increase the
current size of the data file to a reasonable amount and you should adjust the
growth increment accordingly.

Incorrect answers:

A: Truncating the transaction log will remove the older transactions that have been
committed to CK_Orders, but it will not reallocate space on a disk to a data file.
B: The sp_spaced used stored procedure is used to determine how much space a
database or table holds. It will not be of use in this situation.
C: Backing-up CK_Orders will not alleviate the space issues at hand. And also the
NO_TRUNCATE option cannot be specified when you perform a transaction log backup.
Reference:

QUESTION 19

You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows Server 2000 and all database servers run SQL
Server 2000. The Certkiller .com network contains a SQL Server 2000 database
server named Certkiller -DB01. Certkiller -DB01 hosts a database named
CK_Orders. Several data capturers enter data into the CK_Orders database
everyday. The Simple Recovery Model is implemented for the CK_Orders database.
A Full backup of the CK_Orders database is performed every Saturday at 2:00
P.M. You are concerned that data loss may occur should Certkiller -DB01 suffer a hard
disk failure. You decide to implement additional backups of the CK_Orders
database on a daily basis. You want to minimize the amount of time required for the
backups as well as the time required to restore the database.

What should you do?

A. Perform differential backups of the CK_Orders database on every week day.

B. Switch the CK_Orders database to the Full Recovery Model.

C. Perform a Full backup of the CK_Orders database on every week day.

D. Perform incremental backups of the CK_Orders database on every week day.

Answer: A

Explanation: To minimize the amount of time required to perform the backups, you
need to perform differential backups on the week days. Differential backup ups only
backs up the data that has changed since the last Full backup.

Incorrect answers:

B: The recovery model will not affect the time required for the backup or the restore.
C: Restoring data from transaction log backups will not minimize restore times.
D: SQL Server 2000 does not support incremental backups.

QUESTION 20

You work as the 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 Server 2000. The Certkiller .com
network contains two SQL Server 2000 database servers named Certkiller -DB01
and Certkiller -DB02, and a member server named Certkiller -SR33. A database
mirroring session operating in synchronous mode is in operation between
Certkiller -DB01 and Certkiller -DB02, with Certkiller -DB01 being the
principal database server. Certkiller -DB01 hosts a database named CK_Sales that
stores sales data from Certkiller .com's e-Commerce Web site.
One Monday morning you discover that the CK_Sales database is unavailable. You
discover that Certkiller -DB01 is offline while Certkiller -DB02 is online;
however, failover to Certkiller -BD02 did not occur. This failure has adversely
affected the profitability of Certkiller .com's e-Commerce division. You need to
improve the availability of the CK_Sales database. You are unable to acquire any
additional hardware.
What should you do?

A. Configure Certkiller -DB01 and Certkiller-DB02 in a failover cluster with two
active nodes.

B. Configure the mirrored database session to run in asynchronous mode.

C. Install SQL Server 2000 on Certkiller -SR33 and configure it as a witness server.

D. Configure the mirrored database to operate in high-safety mode.

Answer: C

Explanation: High-safety mode supports synchronous operation in which all transactions must be committed to both partners. This provides zero-loss of data
but requires a witness to provide automatic failover.

Incorrect answers:

A: Failover clustering would require additional hardware in the form of a shared
external hard disk drive. You, however, are unable to acquire additional hardware.
Database mirroring is a software solution, but requires a witness server to provide
failover.
B: In Asynchronous mode a transaction is committed to the principal database
without requiring the principal database to wait for the mirror database to write the
transaction log to disk. This maximizes performance for the mirrored database but
at the expense of high-availability.When the principal server of a mirrored database in asynchronous operation fails, you must manually stop the mirror session, update the database and begin a new mirroring
session.
D: High-safety mode supports synchronous operation in which all transactions must
be committed to both partners. This provides zero-loss of data but requires a witness to provide automatic failover.