When I became our companies MSSQL DBA I inherited an smooth running machine. My predecessors were extremely talented and their scripts did everything we needed done. But I can’t just live off their hard work, I need to leave my mark on the environment. So I am tweaking some of their scripts to get rid of what annoys me. The current task is old backup files. The scripts they wrote do this awesome thing that archives a backup if the archive bit has not been cleared. This is so cool. If NetBackup has a problem, the old backup file that hasn’t gone to tape/disk is moved to an archive folder and then the new backup file is created. But what happens when after the file is backed up and the archive bit is cleared. It just sits on the disk and is backed up again with every full backup that occurs. That makes the backups bigger and take longer. So I added a job that deletes all *.bak files that have their archive bit cleared. That was so much easier than I expected it to be.
exec xp_cmdshell ‘del d:\mssql\backup\*.bak /A:-A /S /F’
I run this command 15 minutes before I do the nightly backup. It frees up space so I get less alerts of full volumes. It decreases the NetBackUp full backup time and more backups can happen within their windows. Unfortunately the DBAs before me decided that transaction log backups and database backups should have an extension of .bak. Since I want to treat transaction logs differently I have to go back and change the backup extension. So I’m slowly working through the databases to make that change. So once that is done, I can start cleaning up my backup volumes.
Tags: Database, SQL Server, t-sql