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. Which SQL Server 2008 feature allows you to compress data at the row or page level to reduce storage space?

    Answer: Data compression

    Data compression in SQL Server 2008 supports ROW and PAGE compression, reducing storage and potentially improving I/O performance.

  2. You need to monitor SQL Server performance over time and capture a baseline. Which tool is BEST suited for this?

    Answer: Data Collector and Management Data Warehouse

    The Data Collector with Management Data Warehouse (MDW) in SQL Server 2008 is designed for automated, long-term performance data collection and baseline analysis.

  3. A database is set to the BULK_LOGGED recovery model. What happens to bulk operations in this model?

    Answer: They are minimally logged, reducing log space usage

    BULK_LOGGED recovery model minimally logs bulk operations (like BULK INSERT, SELECT INTO) to reduce log growth while still supporting log backups.

  4. Which system view in SQL Server 2008 shows the current wait statistics for the SQL Server instance since it was last started?

    Answer: sys.dm_os_wait_stats

    sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed, helping identify performance bottlenecks.

  5. What is the purpose of the 'suspect_pages' table in the msdb database?

    Answer: To track database pages that encountered 823 or 824 errors during reads

    The msdb.dbo.suspect_pages table records pages that failed with I/O errors (error 823/824/829), helping DBAs identify potential disk or corruption issues.

  6. You want to implement a maintenance solution that automatically rebuilds indexes with fragmentation above 30%. Which approach is MOST appropriate?

    Answer: Use sys.dm_db_index_physical_stats in a SQL Agent job with conditional ALTER INDEX REBUILD

    Querying sys.dm_db_index_physical_stats to check avg_fragmentation_in_percent and conditionally running ALTER INDEX REBUILD is the modern, recommended approach for index maintenance.

  7. Which SQL Server 2008 feature enables row-level auditing of security-relevant events and writes results to the Windows Security event log or a file?

    Answer: SQL Server Audit

    SQL Server Audit, introduced in SQL Server 2008, provides a structured mechanism to track and log server and database-level events for compliance.