2008 Sql Server Chapter 2

Front 1

After you create the database, you should mark the ______ filegroup as the default filegroup.

Back 1

second

Front 2

You change the default filegroup by using the
following command:

Back 2

ALTER DATABASE <database name> MODIFY FILEGROUP <filegroup name> DEFAULT

Front 3

You define the location for FILESTREAM data in
a database by designating a filegroup within the database to be used for storage with the ___________ property.

Back 3

CONTAINS FILESTREAM

Front 4

A common practice for tempdb is to create one ___ per processor

Back 4

file

Front 5

What are the types of files that you create for databases and what are the commonly used file extensions?

Back 5

You can create data and log files for a database. Data files commonly have either an .mdf or .ndf extension, whereas log files have an .ldf extension.

Front 6

What is the purpose of the transaction log?

Back 6

The transaction log records every change that occurs within a database to persist all transactions to disk.

Front 7

You have a reference database named OrderHistory, which should not allow any data to be modified. How can you ensure, with the least amount of effort, that users can only read data from the database?

A. Add all database users to the db_datareader role.
B. Create views for all the tables and grant select permission only on the views to
database users.
C. Set the database to READ_ONLY.
D. Grant select permission on the database to all users and revoke insert, update, and
delete permissions from all users on the database.

Back 7

c

C. Correct: Unless the database is in READ_ONLY mode, members of the db_owner role can
still change data in the database.

Front 8

How do you restrict database access to members of the db_owner role and terminate all active transactions and connection at the same time?

Back 8

ALTER DATABASE <database name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.

Front 9

What backups can be executed for a database in each of the recovery models?

Back 9

You can create full, differential, and file/filegroup backups in the Simple recovery model. The Bulk-logged recovery model allows you to execute types of backups, but you cannot restore a database to a point in time during an interval when a minimally logged transaction is executing. All types of backups can be executed in the Full recovery model.

Front 10

The AUTO_SHRINK option shrinks a database file when there is more than ____percent of free space in the file.

Back 10

25

Front 11

You can track and log damaged pages by enabling the ____________option.

Back 11

PAGE_VERIFY CHECKSUM

Front 12

You are the database administrator at Blue Yonder Airlines and are primarily responsible for the Reservations database, which runs on a server running SQL Server 2008. In addition to customers booking flights through the company’s Web site, flights can be booked with several partners. Once an hour, the Reservations database receives multiple files from partners, which are then loaded into the database using the Bulk Copy Program (BCP) utility. You need to ensure that you can recover the database to any point in time while also maximizing the performance of import routines. How would you configure the database to meet business requirements?

A. Enable AUTO_SHRINK
B. Set PARAMETERIZATION FORCED on the database
C. Configure the database in the Bulk-logged recovery model
D. Configure the database in the Full recovery model

Back 12

D

Correct: The full recovery model ensures that you can always recover the database to any point in time.

Front 13

Data pages are 8 kilobytes (KB) in size, but SQL Server divides a page into ____ blocks of ____ bytes apiece when performing write operations.

Back 13

16
512

Front 14

Which option should be enabled for all production databases?

Back 14

PAGE_VERIFY CHECKSUM

Front 15

What checks does DBCC CHECKDB perform?

Back 15

DBCC CHECKDB checks the logical and physical integrity of every table, index,and indexed view within the database, along with the contents of every indexed
view, page allocations, Service Broker data, and database catalog.

Front 16

DBCC CHECKDB is used to check the ____ and _____ consistency of a database.

Back 16

logical
physical

Front 17

Which commands are executed when you run the DBCC CHECKDB command? (Check all that apply.)

A. DBCC CHECKTABLE
B. DBCC CHECKIDENT
C. DBCC CHECKCATALOG
D. DBCC FREEPROCCACHE

Back 17

A, C

A. Correct: A DBCC CHECKDB command executes DBCC CHECKTABLE, DBCC CHECKALLOC,
and DBCC CHECKCATALOG.

C. Correct: A DBCC CHECKDB command executes DBCC CHECKTABLE, DBCC CHECKALLOC,
and DBCC CHECKCATALOG.