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

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.
Free 30-Day Python Certification Bootcamp is Live. Join Now!

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.

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
I’m Manish - Founder and CTO at Sanfoundry. I’ve been working in tech for over 25 years, with deep focus on Linux kernel, SAN technologies, Advanced C, Full Stack and Scalable website designs.

You can connect with me on LinkedIn, watch my Youtube Masterclasses, or join my Telegram tech discussions.

If you’re in your 40s–60s and exploring new directions in your career, I also offer mentoring. Learn more here.