Term
| What are the recovery models available in SQL Server 2008? |
|
Definition
| Full, Bulk-logged, and Simple |
|
|
Term
| How often are database changes purged from the transaction log using the Full Recovery model? |
|
Definition
| The changes remain indefinitely. They are removed only by executing a transaction log backup. |
|
|
Term
| What operations are only minimally-logged within a database set to the Bulk-Logged recovery model? |
|
Definition
- BCP
- BULK INSERT
- SELECT…INTO
- CREATE INDEX
- ALTER INDEX…REBUILD
|
|
|
Term
| How does the Simple Recovery Model differ from the Full Recovery Model? |
|
Definition
-
Using the Simple Model, each time the database checkpoint process executes, the committed portion of the transaction log is discarded. (The Full Recovery Model retains changes indefinitely.)
-
It is not possible to issue a transaction log backup for a database in the Simple Recovery Model.
|
|
|
Term
| What option needs to be enabled to allow for the discovery and logging of data pages damaged during a disk write? |
|
Definition
|
|
Term
| What, aside from executing a page restore, does SQL Server 2008 do to fix a damaged data page? |
|
Definition
| If the database is participating in a database mirroring session, SQL Server 2008 automatically replaces the page with a copy of the page from the mirror. |
|
|
Term
| What are the five database options that enable certain actions to occur automagically? |
|
Definition
| AUTO_CLOSE, AUTO_SHRINK, AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNCH |
|
|
Term
| Which auto option sets SQL Server to shut down a database that has had its last connection closed, releasing all resources related to the database? |
|
Definition
|
|
Term
| If AUTO_SHRINK is enabled, a data file that has more than __% free space will shrink to reclaim that disk space. |
|
Definition
|
|
Term
| A database in an EMERGENCY state can be accessed only by a member of which role? |
|
Definition
|
|
Term
| What is the only command that can be executed in an EMERGENCY state? |
|
Definition
|
|
Term
| In what access mode does SQL Server remove any transaction log file that is specified for the database? |
|
Definition
|
|
Term
| When a database is set to RESTRICTED_USER mode, which member roles are allowed to access the database? |
|
Definition
| db_owner, dbcreator, and sysadmin. |
|
|
Term
| What is the normal operational mode for most databases? |
|
Definition
| ONLINE (status), READ_WRITE (modification level), and MULTI_USER (access level). |
|
|
Term
| What will assist the Query Optimizer in building more efficient query plans? |
|
Definition
| Statistics, created automatically by enabling the AUTO_CREATE_STATISTICS option. |
|
|
Term
| What database option associates version numbers with each row in a table, to be incremented each time a change is made in that row? |
|
Definition
|
|
Term
| True or False: Applications gain a significant benefit when database calls are parameterized. |
|
Definition
True.
SQL Server caches the query plan for every query that is executed; when new queries are executed, they are compared to all cached query plans using a string-matching algorithm. A query that is parameterized has a higher probability of being matched because the query string doesn't change even if the values being used vary. Reusing query plans is more efficient. |
|
|
