Hotspot Question You have a SQL Server on Azure Virtual Machines instance that hosts a database named DB1. You need to configure the autogrow and autoshrink settings for DB1. Which statements should…

Question

Exhibits

Answer Area

• Autogrow:
  ALTER DATABASE MODIFY File and Filegroup optionsALTER DATABASE SCOPED CONFIGURATIONALTER DATABASE SET options
• Autoshrink:
  ALTER DATABASE MODIFY File and Filegroup optionsALTER DATABASE SCOPED CONFIGURATIONALTER DATABASE SET options

Explanation

The question tests knowledge of SQL Server's ALTER DATABASE statement to configure autogrow for database files and autoshrink as a database option.

Approach. For 'Autogrow', the correct selection is ALTER DATABASE MODIFY File and Filegroup options. Autogrow settings (initial size, growth increment, maximum size) are properties of individual data and log files within a database. These are configured using the ALTER DATABASE MODIFY FILE statement, which falls under the 'File and Filegroup options' category.

For 'Autoshrink', the correct selection is ALTER DATABASE SET options. Autoshrink is a database-level option that is enabled or disabled for the entire database. Database options like AUTOSHRINK, AUTO_CLOSE, READ_ONLY, etc., are configured using the ALTER DATABASE SET statement.

Common mistakes.

• common_mistake. Selecting ALTER DATABASE SCOPED CONFIGURATION for either option is incorrect because SCOPED CONFIGURATION is used for specific database-level configurations like MAXDOP, LEGACY_CARDINALITY_ESTIMATION, etc., which are distinct from file properties or general database SET options.

Selecting ALTER DATABASE SET options for Autogrow is wrong because Autogrow is a file-specific property, not a database-wide SET option.

Selecting ALTER DATABASE MODIFY File and Filegroup options for Autoshrink is wrong because Autoshrink is a database-wide option, not a property of an individual file or filegroup.

Concept tested. SQL Server DDL (Data Definition Language) commands, specifically the ALTER DATABASE statement and its various clauses for managing database files (MODIFY FILE) and setting database options (SET). Understanding the distinction between file-level properties, database-level options, and database-scoped configurations.

No community discussion yet for this question.