So far, no one has found exercise to be beneficial to servers.Â Purposeless repetitive motion may be good for human muscles, but your SQL Server instance experiences no gain for the pain.
Here’s a good example: taking useless backups.
(“Did she sayÂ useless backups? I’ve never heard of such a thing!” Yeah, just wait.)
Backup file names are critical
Traditionally, backup files are named after the database and the backup type, and given a timestamp. So you’ll see something like master_FULL_20170101.bak. If you like to stripe your backups, you might name the files something like 1of5MasterFull20170101.bak, and so on.
But I haveÂ run across shops that takes backups without bothering to time stamp the file name: master_FULL.bak. Â These shops either overwrite each backup file, with each successive backup (using INIT and FORMAT), or add to the backup set (which I find mildly annoying, but to each their own).
The problem withÂ using the same backup file name over and over is if you have a cleanup mechanism that deletes old backup files!
The same-name cleanup issue
Let’s say that in your shop, you have Minion Backup (MB) installed and running with the following options:
- INIT isÂ enabled
- FORMAT is enabled
- Backup file retention (in hours) is 48, so we’ll keep 2 days’ worth of backups
- Backup name is set to %DBName%%BackupType%.bak, which works out to DB1Full.bak for a full backup of DB1.
Here is what happens:
- On day 1, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.
- On day 2, MB takes a backup of DB1, which overwrites the file \\NAS1\SQLBackups\DB1Full.BAK.
- On day 3, MB takes a full backup of DB1 (which overwrites the same file). And then the delete procedure sees that it has a file from day 1 (>48 hours ago) that needs deleting. And so it deletes \\NAS1\SQLBackups\DB1Full.BAK. Remember, this is the file that MB has been overwriting again and again.
- On day 4, MB takes a backup of DB1, to \\NAS1\SQLBackups\DB1Full.BAK.. Then, it sees that it has a file from day 2 that needs deleting, and so deletes \\NAS1\SQLBackups\DB1Full.BAK.
See? From Day 4 on, we’re creating a backup file just to delete it again!
Fixing the issue if you have MB
One excellent way to figure out if you have this problem is to notice that, hey, you don’t have any backup files. Another indicator in Minion Backup isÂ if you see “Complete: Deleted out-of-process. SPECIFIC ERROR:Cannot find path ‘…’Â because it does not exist.” in theÂ Minion.BackupFiles Status column.
But the real smoking gun is if you haven’t time stamped your backup files in Minion.BackupSettingsPath.Â Here’s how to fix that:
That will give each file its own name, and eliminate the chance of this ever happening.
And it willÂ prevent your server from performing useless exercise. Me? I’m going toÂ hit the gym.