This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “SQL Server Questions and Answers – Database Maintenance”.
1. The SQL Server Maintenance Solution comprises scripts for running __________ maintenance on all editions of Microsoft SQL Server.
a) backups
b) integrity checks
c) index
d) all of the mentioned
View Answer
Explanation: The solution is based on stored procedures, the sqlcmd utility, and SQL Server Agent jobs.
2. Point out the correct statement related to database maintenance :
a) Performing SQL Server Maintenance with Maintenance Window
b) Performing SQL Server Maintenance with No Maintenance Window
c) Performing SQL Server Maintenance with Scrip Maintenance Window
d) None of the mentioned
View Answer
Explanation: When the production SQL Server is running on a 24X7 basis or the maintenance window is not long enough to support the maintenance.
3. Which of the following is used to determine the fragmentation for tables and indexes ?
a) DBCC SHOWCONTIG
b) DBCC CHECKDB
c) DBCC CHECKCATALOG
d) None of the mentioned
View Answer
Explanation: In SQL Server 2005 DBCC SHOWCONTIG remains a viable short term option to determine the database fragmentation.
4. Which of the following is used to Validate the database objects ?
a) DBCC SHOWCONTIG
b) DBCC CHECKDB
c) DBCC CATALOG
d) None of the mentioned
View Answer
Explanation: DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables.
5. Point out the wrong statement :
a) The command DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database
b) The command DBCC CHECK checks the logical and physical integrity of all the objects in the specified database
c) The command CHECKDB checks the logical and physical integrity of all the objects in the specified database
d) None of the mentioned
View Answer
Explanation: DBCC CHECKDB has some performance quirks based on the schema of the database being checked.
6. Which of the following command defragments clustered and secondary indexes of the specified table ?
a) DBCC SHOWCONTIG
b) DBCC CHECKDB
c) DBCC CATALOG
d) DBCC INDEXDEFRAG
View Answer
Explanation: DBCC INDEXDEFRAG is an online operation, so it does not hold long-term locks that can block running queries or updates.
7. ______________ runs UPDATE STATISTICS against all user-defined tables in the current database.
a) sp_updatestats
b) DBCC CHECKDB
c) DBCC CATALOG
d) DBCC INDEXDEFRAG
View Answer
Explanation: sp_updatestats updates all statistics for all tables in the database.
8. Purpose of DBCC DBREINDEX command is to :
a) Rebuilds one or more indexes for a table in the specified database
b) Rebuilds only one index for a table in the specified database
c) Displays fragmentation information for the data and indexes of the specified table
d) All of the mentioned
View Answer
Explanation: The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time.
9. What types of database maintenance should be executed ?
a) Update SQL Server’s usage
b) Validate the database objects and system catalogs are free of corruption
c) Determine the fragmentation for tables and indexes
d) All of the mentioned
View Answer
Explanation: The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Server 2014.
10. Create a Database Maintenance Plan that creates a ________ backup within SQL Server.
a) Partial
b) Snapshot
c) Full
d) None of the mentioned
View Answer
Explanation: Creating full backup is minimum recommendation for good database maintenance.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
