The system reboots are only during scheduled downtime/upgrades which happen to be every few months. Are you suggesting it is not a SQL issue and maybe something with the O.S.? Can you expand on that? Is having SQL maintain transaction log backup deletes for 230+ databases that get transaction log backups every 15 minutes too much for it to handle? The job simply deletes .trn files older than 2 days and that is the only step.
I should add the transaction log backups themselves only take 3-5 minutes to complete and run every 15 minutes.
I have a different job that deletes the .bak files older than 2 days and it completes in acceptable times.
I don't reboot to fix the slow transaction log deletes as I use a different SQL job using Forfiles (http://www.mssqltips.com/sqlservertip/1618/using-the-forfiles-command-to-delete-sql-server-backups) to delete the transaction logs when the other maintenance plan job slows. I would like to find the source of the slowness and continue to use SQL maintenance to delete the transaction logs if possible.
Like I mentioned in a previous post if I open a folder containing the transaction log backups while the maintenance plan job is running it will literally delete one .trn file per second. This happens on small and/or large (max few hundred MB's each but on average a few MB's each) transaction log backups. If I were to reboot the server (or possibly just restart SQL services) the same exact job looking in a different folder will delete all the old logs almost immediately. I never have this problem using the Forfiles script in the above link as it uses EXEC xp_cmdshell and essentially a DOS command to delete the .trn files.
Please let me know if anyone needs clarification or additional info.
Rama Chandra Gowtham. Peddada (1/3/2013)
Have you tried re-cycling SQL Services instead of System Reboot. This would differentiate if the problem is at the instance level or at the server level.