I have been working with a client recently who purchased a nice top of the line de-duplication backup device in order to synchronize backups between two data centers. This device is very nice in how it will de-duplicate data to reduce storage needs and also compress the data. It will then synchronize the data to a secondary device hundreds of miles away.

These types of devices are not cheap and work very efficiently. My client got their SQL Server environment migrated to the new backup appliance and found a nice vulnerability within the device. Their previous backup solution maintained the retention policies whereas this new device did not. After using the new solution for a few months they notice performance decreasing almost to the point of moving back to their old solution.

After a couple of days of research into the issue it was ruled that the root cause was having too many files in the backup directory. Since several of the servers contained databases with low RPO’s, they were making lots of transaction log backups. Several of the tlog backup folders contained 10′s of thousands of backup files all with similar names.

Once the tlog folders were purged the system returned to a normal operational level. It was explained to the client by the vendor that they shouldn’t have more than 1000 files per folder and no more than 1000 subfolders.

The first lesson is that anytime you fundamentally change a solution of a critical process, you need to have a process in place to account for every aspect of the previous solution. Had this client built a proper purge process, they would not have brought a top of the line backup device to its knees.

The second lesson is that if you are making frequent transaction log backups for a system with multiple databases on it, you should consider writing the transaction logs to individual sub folders. The best practice would be to have a tlog folder with a sub folder for each database.  Having this in place will limit the number of files per directory.