SQL Server Questions and Answers – Database Maintenance

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

Answer: d
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

Answer: b
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

Answer: a
Explanation: In SQL Server 2005 DBCC SHOWCONTIG remains a viable short term option to determine the database fragmentation.
advertisement
advertisement

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

Answer: b
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

Answer: a
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

Answer: d
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

Answer: a
Explanation: sp_updatestats updates all statistics for all tables in the database.
advertisement

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

Answer: a
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

Answer: d
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.
advertisement

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

Answer: c
Explanation: Creating a full backup is a 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.

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.