9 Instance and database management

In this chapter, we will discuss common maintenance and configuration mistakes made by accidental database administrators (DBAs). As part of this topic, we will discuss the impact of autoshrinking databases before looking at some misconceptions around transaction log files, which lead to issues such as degraded performance.

Next, we will discuss capacity planning. This is a task that many DBAs fail to perform, and we will look at the potential consequences. We will then explore some common mistakes around scripting and automation, including the use of cursors, and a complete lack of maintenance automation.

Finally, we will explore the failure to patch our servers. We will think about the reasons for not patching and the consequences of doing so. We will also discuss how to avoid making this mistake.

Many of the topics in this chapter focus on the day-to-day work of a DBA. Therefore, not all topics have a direct impact on the business. It is important to remember, however, that there is most certainly an indirect impact on the business. For example, if we fail to patch, then there is an increased risk of the servers being attacked by bad actors, leading to massive disruption and reputational damage for the business.

9.1 #43 Autoshrinking databases

9.2 #44 Failing to rebuild indexes after data file shrink

9.3 #45 Relying on autogrow

9.4 #46 Using multiple log files

9.5 #47 Allowing logs to become fragmented

9.6 #48 Failing to capacity plan

9.7 #49 Always placing TempDB and log files on dedicated drives

9.8 #50 Not regularly checking for corruption

9.9 #51 Failing to automate

9.10 #52 Using cursors for administrative purposes

9.11 #53 Failing to patch

Summary