MSCE Interview Questions and Answers

CK_Sales contains two data files named CK_Sales1 and CK_Sales2. Both are
located on drive E. Drive E has 50 MB of free space available. The Sales department
members gain access to the database via a custom application that allows them to
create new tables and indexes. Thus all user tables and their indexes were created
by the Sales department members using this custom application only. The custom
application does not make provision for users to specify a filegroup on which to
place the objects that are creating.

You received a report from the Sales department members that they are unable to
create new objects. You need to address this issue and thus need to provide
additional disk space to enable users to continue working. You want to address this
issue using the least amount of administrative effort.
What should you do?

A. Automatic file growth should be disabled for the Primary filegroup.
B. Additional transaction log files should be created on drive E.
C. Additional files should be created in the Primary filegroup on drive E.
D. Secondary should be configured as the default filegroup.
E. The maximum allowed size for the secondary file should be reduced on the Properties sheet for the database.

Answer: D

Explanation: In the event of no filegroup explicitly specified as the default filegroup, then Primary will become the default filegroup resulting in all new tables and indexes that are created to be placed in the default filegroup. The custom application does not allow for users to specify a filegroup. Thus one can conclude that the reason why the sales Department members cannot create new objects must be that the Primary filegroup is nearly full. It is also mentioned in the question that all user tables and indexes have been created using the custom application only. Thus no objects should currently reside on Secondary, and thus its file would be empty. To remedy the situation with the least amount of effort should then be to specify Secondary as the default filegroup. This should allow users to create new tables and indexes which will then be assigned to Secondary.

Incorrect answers:
A: .Disabling automatic file growth for Primary filegroup will not provide additional
disk space.
B: .Creating additional log files will result in more than the required administrative effort
to remedy the situation.
C: This situation can be remedied by creating an additional file in the Primary
filegroup on drive E. But drive E only has 50 MB of available free space.
E: You cannot reduce the maximum allowed size for the secondary file on the
Properties sheet for the database.

QUESTION 25
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional 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_Sales that stores
sales and product data. The Simple Recovery Model is implemented for the
CK_Sales database. A Full backup of the CK_Sales database is performed every
Saturday at 8:00 P.M.
The sales data is updated frequently throughout the day. Over the last six months
the CK_Sales database has grown considerably. At present the Full backup requires
6 hours to complete. 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_Sales database on a daily basis. However, the backup may only
run between midnight and 4:00 A.M. on week nights.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Schedule a Full backup of the CK_Sales database at 12:00 P.M. every week night.
B. Schedule a differential backup of the CK_Sales database at 12:00 P.M. every week
night.
C. Create two filegroups. Backup a different filegroup every other week night.
D. Backup the transaction log every week night.

Answer: C, D

Explanation: Creating two filegroups and backing up a different filegroup every
other week night will allow you to backup the database within the given time frame
during the week.

You should also backup the transaction logs every night to ensure full
recoverability as only one filegroup is backed up each night.

Incorrect answers:
A: A Full backup requires six hours to complete but the backup can only run from
12:00 P.M. until 4:00 P.M. during the week. Thus there is insufficient time to complete a Full backup during the week.
B: A differential backup will backup all data that has changed since the last Full backup. Towards the end of the week the differential backup may take quite some depending on the number of changes made since the last Full backup and may require more than 4 hours to complete.


QUESTION 26
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
following exhibit illustrates the database properties of the CK_Sales database:
Exhibit:


All Certkiller .com users interact with the database via a custom database application that enables certain Sales department members the ability to create new tables and indexes on those tables. Unfortunately the custom application does not make allowance for users to specify a filegroup in which to place new objects. To compound problems, you notice that drive E is running out of space. To this end you
need to mitigate this problem so as to ensure that users will be able to continue
creating tables and indexes in the database.
What should you do?

A. Drive E should be compressed.
B. Secondary should be specified as the default filegroup.
C. Additional files should be created in Secondary on drive E.
D. Primary filegroup's primary data file requires shrinking.
Additional data files should be created in Primary filegroup on Drive E

Answer: B

Explanation: Since the Sales Department members use a custom application to access
SQL Server 2000 and create new objects it stands to reason that all new objects are
automatically placed in the default filegroup. Originally SQL Server designates the
Primary filegroup as the default filegroup. It is also mentioned in the question and
on the exhibit that another filegroup exists on drive E. as an administrator you can
specify another filegroup as the default filegroup. Thus if you specify Secondary as the default filegroup, then new objects will be created on that filegroup, which should contain sufficient free space because all new objects to date had been creates only by using the custom application.

Incorrect answers:
A: SQL Server data files cannot reside on compressed drives.
C: The question pertinently states that only the custom application is used to create
new objects. Thus it does not matter how many files were created in Secondary or
how much free space were available on drive E. the custom application automatically
places new objects on the default filegroup which is currently Primary.
D: The growth on the primary data file is not currently restricted. Thus the growth
of the primary data file is the most likely reason which drive E is running out of
space. Hence there is no free disk space that could be returned to the operating
system by shrinking. And thus you also cannot create additional data files.

QUESTION 27
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional 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_Staff. The Full Recovery Model is
implemented for the CK_Staff database.
Certkiller .com acquires another company named TestLabs.com. You import staff
data from the new company into the CK_Staff database. Soon after ward you notice
that Certkiller -DB01 is performing quite poorly. You discover that the
transaction logs for the CK_Staff database has increased by almost five times in size
and has consumed most of the free disk space. To this end you need to recover disk
space as soon as possible.
What should you do?

A. The transaction log files should be backed up.
B. Switch to the Simple Recovery Model.
C. The DBCC SHRINKFILE Transact-SQL statement should be executed.
D. The transaction log files should be truncated.

Answer: C

Explanation: TheDBCC SHRINKFILE statement is used to reduce the log files to a
specified size.

Incorrect answers:
A: Backing up the log file clears the log file but does not reduce the physical size of the transaction log file.
B: Switching to the Simple Recovery Model will not reduce the physical size of the
transaction log file.
D: Truncation reduces the logical size of the transaction log file but it does not reduce the physical size of the transaction log file.

QUESTION 28
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows NT Server 4.0 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_Customers. The CK_Customers
database has almost completely filled the available space. An original size of 3 GB
had been allocated when the data file was created, and this data file has since grown
to 10 GB.
You this need to create another database, but you cannot add any new disks to the
computer. There is no free space on the other drives. Thus you archive a portion of
the older data to a decision-support system (DSS) database. Thereafter you remove
the archived data from CK_Customers. However, you find that you cannot create
the new database since the size of the data file has not decreased. You need to ensure that you have enough disk space to create another database.
What should you do?

A. The DBCC SHRINKFILE (DataFile, 2000) statement should be executed.
B. The Read-Only option should be enabled on the CK_Customers database.
C. The Transaction Log should be truncated.
D. The Space available parameter should be set to 1 GB on CK_Customers database.

Answer: A

Explanation: The DBCC SHRINKFILE (DataFile, 2000) statement causes free
space to be relocated to the end of the file and then reclaimed. This free space can
then be returned to the operating system.

The remaining file size should then be approximately 2,000 MB which will accommodate
the creation of a new database.

Incorrect answers:
B: Enabling the Read-Only option will permit users to query the database; it does
not prevent them from manipulating data. Thus there would be no gain in space to
create a new database.
C: Truncating the Transaction Log removes committed transaction from the log,
but it does not reclaim free space that is available in the log. You need to shrink the log. However, this could result in poor database performance.
D: No gains in Space available parameter appears on the General tab in the
database Properties sheet. This parameter cannot be configured.

QUESTION 29
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_Customers.
Certkiller -DB01 has its operating system located on drive C, its program files
located on drive D and its only data file and the transaction log file located on drive E. Drive E has only 250 MB of free space remaining and Drive F has 8 GB of free
space.

You received instruction to load 1 GB of new data into the existing tables in
CK_Customers. CK_Customers is configured for the Bulk-Logged Recovery Model.
You need to make use of the BULK INSERT statement to insert the new data into
the existing tables on CK_Customers. You thus need to prepare the database to
accommodate the new data since there is only 250 MB of free space on the drive on
which the data file and transaction log file is located.
What should you do?

A. You need to allow unrestricted growth for the data file.
B. A new transactional log file should be created on drive F.
C. A new data file should be created in the Primary filegroup on drive F.
D. A new filegroup should be created on drive F.

Answer: C

Explanation: It is mentioned that there are not enough free space on Drive D to
accommodate the new data that must be loaded into the tables on CK_Customers.
Thus you need to create a new data file in the Primary filegroup on drive F since
drive F contains 8 GB of free space. Space for new data is automatically allocated
proportionately to the amount of free space available from both files.

Incorrect answers:
A: Allowing unrestricted growth for the primary file would not be an effective solution as drive E does not contain sufficient free space.
B: It is mentioned in the question that CK_Customers is configured for the
Bulk-Logged Recovery Model. This means that the bulk load operation will not be
fully logged and the transaction log will not require much space for growth.
Creating a new transactional log file on drive F will not accommodate new data.
Data files and transaction log files cannot be places on a compressed drive or a network drive.
D: Adding a new filegroup will not have any objects in it and the question states
pertinently that the new data should be loaded into the existing tables on
CK_Customers. These existing tables are located in the primary file in the Primary
filegroup.

QUESTION 30
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_Customers. The CK_Customers
Categories table is illustrated in the following exhibit:
Exhibit:




A Certkiller .com user named Mia Hamm is responsible for updating information on
this table. She needs to delete the 'pizza' value in the CatName column since this
item is no longer on the menu. Certkiller .com is replacing pizza with pasta which is
currently being tested. Mia Hamm intends making use of the following statement to
carry out her task:

UPDATE Categories SET CatName = NULL
Unfortunately she finds that the statement fails. You need to enable Mia Hamm to
delete the 'pizza' value in the CatName column.
What should you do?

A. An AFTER DELETE trigger must be created on the CatName column.
B. The Mia Hamm user account should be granted permission to execute the CREATE
PROCEDURE statement. Instruct Mia Hamm to create a stored procedure to replace the value in the CategoryName column with a NULL value.
C. Modify the table to allow nulls in the CatName column.
D. A PRIMARY KEY constraint should be defined on the CatName column.

Answer: C

Explanation: a Null value is not the equivalent of zero, an empty string of a string of zero length; instead it indicates that a particular value is unknown. In the exhibit you can see that the CatName column is not configured to allow null values. If MiaHamm is to be permitted to delete the current value without having to specify a new value, you should change the definition of the CatName column to allow null values by executing the following statement:
ALTER TABLE Categories ALTER COLUMN CatName nvarchar (15) NULL

Incorrect answers:
A: Creating an AFTER DELETE trigger will result in the trigger only being fired after a user successfully executed a delete statement. But this is not possible at the moment
since the CatName column does not allow for null values.
B: This option will not work because the CatName column currently does not allow
null values and any stored procedure that attempted to replace the current value with a null value would thus fail.
D: A PRIMARY KEY constraint enforces entity integrity in a table. Though it can
be created in a table, it will not work in this case since the primary key is defined on the CatID column. And also the column that is defined as a primary key cannot accept null values.