Practice Test Geeks(MCTS) Microsoft Certified Technology Specialist Practice Test
← All MCTS Flashcard Decks

SQL Server 2008 Administration Flashcards

7 cards from real MCTS practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.

Read the first 7 SQL Server 2008 Administration flashcards as text

  1. You need to automate a multi-step SQL Server maintenance job that runs different steps based on the success or failure of previous steps. Which tool provides this capability?

    Answer: SQL Server Agent with job steps and flow control

    SQL Server Agent jobs support step-level success/failure flow control, allowing conditional execution paths between steps within a single job.

  2. What is the purpose of the Resource Governor feature introduced in SQL Server 2008?

    Answer: To manage and limit CPU and memory resources used by different workload groups

    Resource Governor allows DBAs to classify incoming sessions into workload groups and assign resource pools with CPU and memory limits.

  3. Which SQL Server 2008 backup type captures only the changes made since the last FULL backup?

    Answer: Differential backup

    A differential backup captures all data pages modified since the last full backup, making it faster to back up and restore than a full backup.

  4. A user reports they cannot connect to SQL Server using Windows Authentication. The SQL Server error log shows 'Login failed for user. Reason: Token-based server access validation failed.' What is the MOST likely cause?

    Answer: The SQL Server service account does not have permission to query Active Directory for group membership

    This error typically occurs when the SQL Server service account cannot contact a domain controller to validate the user's Windows group memberships for authorization.

  5. Which SQL Server 2008 DMV would you query to find queries currently executing on the server along with their execution plans?

    Answer: sys.dm_exec_requests joined with sys.dm_exec_sql_text and sys.dm_exec_query_plan

    Joining sys.dm_exec_requests with sys.dm_exec_sql_text and sys.dm_exec_query_plan provides both the SQL text and execution plan for actively running queries.

  6. What is the effect of setting a database to AUTO_CLOSE = ON?

    Answer: The database shuts down and releases resources after the last user disconnects

    AUTO_CLOSE causes SQL Server to shut down the database and free its resources when the last user disconnects, incurring overhead when the next user connects.

  7. You need to implement transparent encryption on a SQL Server 2008 database without changing the application. Which feature should you use?

    Answer: Transparent Data Encryption (TDE)

    Transparent Data Encryption (TDE) encrypts the entire database at the page level, protecting data at rest with no application changes required.