Do the UnFun Parts - Deload

 Deload week: Do the Unfun Part.

Working out in the gym, as in the database world as well, is not always flashy, fun or impressive. Sometimes its about doing the mundane, the trivial, putting effort into the little things that don't draw attention but ultimately make you better.

In my current workout program, I'm in Deload week. I am still executing the same movements as the previous 4 weeks, but decreasing the weight to about 75-80% of my maximum values, slowing down the movement focusing heavily on the form and technique to make sure I'm still executing it properly. My body is still working, exerting effort, but more so it's working on solidifying the movement so that when I start increasing the weight again, it know what to do. 

I used to get pretty in my own head during Deload week. "Worried" about how others in the gym would perceive my lifts. "Oh man, he's only lifting X amount of weight. Pfff, weak." That's an absurd thought of course. Number one because who cares what others think? I'm here for me, working on bettering myself. It shouldn't concern me how others look at me. Number 2, I promise you no one in the gym is looking at you, taking notice or caring nearly as much as you think. At least in my gym, everyone has on their own headphones and are dialed in to what they're doing. Unless you're turning blue with a barbell on your neck, they don't notice what weight you do or don't have on there.

I've come to appreciate the active recovery achieved during this week. I'm building the foundation for the next round to come up, making sure my body is right.

In much the same way, we in the database world need to make time to do the unfun stuff. Things like our backups, maintenance plans, DBCC checks, etc. These functions don't draw attention (until they break of course), they're not the big splash you might make when speeding up a slow running report or finding the troublesome query. They're not going to get you the accolades, but they're no less important. 

Backups

Let's start with something so fundamental, it should be one of the first things you do after getting a database up and running. Backups. Taking regular backups to ensure that in the event the database gets corrupted or a table mistakenly gets dropped, you've got a point to go back to. There's lots of ways to approach this.

Full - Takes a complete backup of the database at the point you begin said backup. It will take up a fair amount of storage so plan accordingly if you intend to keep local.
Differential - Backups anything that has changed since the previous Full backup. If you run a Differential today and then again the next day, it'll still be whatever changed from the most previous Full. So Differential files can get pretty large if it's an active database.

Log - A super critical backup type especially in highly active systems. This method backs up the log file only. Anything transaction that has taken place in the database is written to the log file. In the event you need to restore to a particular point in time, log file backups are how you get there. 

In addition to this point in time recovery (PITR for shot), when the log backup runs it removes the transactions from the active log file that it just backed up. This helps keep your log file size in check. If you didn't do any log file backups, eventually your log file will hit the 2 TB maximum size and now you're in trouble as the database is for all intents and purposes inaccessible until that size gets down.

DBCC

This acronym (Database Console Commands) has a wide array of functions and features. Too many to go into detail here. The one specifically I'll mention in terms of maintenance activities is DBCC CHECKDB. This checks the physical and logical integrity of all objects inside the database. It will report back any issues or you can set an option to attempt an automatic repair. If those things don't work though, you may have to refer to your backup strategy and look at doing a restore.

Some notes about this process though. It's very intensive so if you only have a singular instance consider running it in off peak hours. If you have secondary copies of your server or databases available (such as mirrors or Availability Groups), run your checks there. Just make sure the secondary copy is in sync with the primary.

Additionally, if you have SQL Server Enterprise license, you are permitted one additional secondary/read only copy of your server to allow for these types of activities.

Historical Cleanup

Something I feel doesn't get addressed enough is historical cleanup. I'm referring to things such as the SQL Event Log and Agent Job History. Why are those important to cleanup? Well, for one they take up disk space just like any data. So the more you keep, the bigger those log files are going to become. They're stored in whatever folder you installed the SQL System files to. So if you defaulted to C:\Program Files in the install, there is potential impact.  
Additionally though, imagine you want to troubleshoot an error, or look at job history. How long might it take to pull up allllllll of that log data that's not being cleaned out? It's a nuisance right?

Expand the object explorer list, Management section, SQL Server Logs. Right mouse click and select Configure.

On this screen select how many log files to retain and a size if you so choose. Note that when the SQL Services restart a new log file will begin. 

Expand the object explorer list, SQL Server Agent, right mouse click and select properties. Then select the History item. Configure accordingly.

Wrap Up

Not every day in the gym or in your database job can be a record smashing, heavy weight lifting day. Sometimes you need to take a step back to the fundamentals and do the un-fun stuff so that the next session, you crush like a beast! Don't skip out on doing the little things that make the big difference!