With the holiday season upon us for much of the world, I find myself thinking of ways to ensure that we have a quiet season outside of the office. Nobody wants to spend a family get-together fixing problems at work. I know most of you know what I mean. It’s a DBA’s lot in life to spend the holiday reinitializing replication, or restoring a corrupted database, or fixing whatever is broken. A lot of these things can’t be helped. You just have to deal with them when they come. But you can help keep things quiet with a little maintenance and preparedness.

Here are some tips to help your night be a silent one:

1. Backups: Duh! Of course backups is the number 1 thing on the list. Make sure all of your databases are being backed up and giving you what you need to meet your recoverability needs. In most cases, this will be a mixture of full and log backups and possibly even differential backups. Make sure every database has current backups and backups are working successfully.
   
   And don’t forget to back up the system databases!
   
   Bonus points if you use the CHECKSUM option in your backup commands to help ensure that the backups do not contain corrupted data.
2. Consistency Checks: Make sure all databases have completed a consistency check (e.g., DBCC CHECKDB) successfully before you start a long weekend. And make sure that further checks are scheduled to occur on a regular basis.
   
   And don’t forget to check the system databases!
3. Cycle the SQL log: By default, the SQL log only cycles when the SQL service starts up. If you go a long time without needing to restart the SQL service, the log file can get quite large. Good luck finding important messages quickly when the log file is 80 GB.
   
   To do this, just set up a job to run sys.sp_cycle_errorlog on a regular basis, like every 2 or 3 days. I also recommend increasing the number of log files that are retained. By default, 6 archive files are retained. I generally set it to the maximum value of 99. This is important because you don’t want to come back after a long weekend and want to find something that happened 7 days ago only to learn that the file no longer exists.
   
   There isn’t a built-in procedure for changing the number of logs to retain, but you don’t have to use the GUI to change it on each server. Here’s a little SQL snippet to change it:
   
   Transact-SQL

   USE master; -- Increase SQL log retention to 99 (max) Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99;

   1 2 3 4 5 6 7 8

   USE master;   -- Increase SQL log retention to 99 (max) Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99;

4. Script out replication: Fortunately, replication doesn’t change very often. I generally like to script out replication every time I set it up or any time I make a change to it. It’s easy to script out using the GUI. Just save it to a file so that if you have to drop and recreate it, it’s quick and easy.
5. Make sure all databases have enough drive space: Before any long weekend or holiday, make sure all database files have plenty of drive space for at least week to ensure that you won’t need to intervene. You don’t want an alert that the drive is full in the middle of the night or when you’re on the road to Grandma’s house. This one is so easy to avoid.