1

I have inherited a server running SQL Server 2008 R2 and it keeps running out of space. After doing some digging with WinDirStat, I found that it is almost entirely due to the SQL Server backups folder, which has backups going back for years that have never been cleaned up. In SQL Server Management Studio, however, I do not see any defined maintenance tasks for regular backup, so I can find no way to turn them off.

Per this thread, it looks like it safe for me to go remove those old backup files, so that will deal with my imminent and routine space problems. I would like to confirm that if possible with someone who actually knows what they're doing.

Beyond that, can anyone point me in the right direction for figuring out how these backups are defined? With no defined maintenance tasks, I'm at a loss as to how they keep happening. I'd like to adjust the automatic backup cadence and automate some clean-up to help keep this under control in the future.

Improve this question
0

1 Answer 1

Reset to default
2

You may have a SQL Server Agent job running that performs backups. You can look into the jobs in SSMS (Under SQL Server Agent). Note that if your SQL Server instance is Express edition this won't be the case since SQL Server Agent is not supported in Express.

There are several third party backup agents that will do SQL Server backups. Some are specific to SQL Server, some support SQL Server as well as the host OS. What is used to backup the host server? It could be creating the db backups also. Look at the software installed on the server to see if there is another backup package install (either a full package or an agent).

Another place to look would be under Task Scheduler to see if there is a scheduled task running backups there (assuming your host OS is Windows).

Regarding deleting the backup files - if all of the backups are full backups then you can safely delete whichever ones you want. Usually full backups have a .bak extension. If some are differential backups (usually with an extension of .dif) then you need to keep the first full backup taken prior to that differential along with the differential.

Improve this answer
2
  • Thanks for that and for the tip on SQL Server Agent. I will look there. As near as I can tell, none of these - going all the way back to 2015 - have a .BAK extension. They all appear to be extensionless. Is this some bizarre artifact of how the backups are being taken, or does it mean something else? Based on the file sizes, they look they well could be a mix of full backups and differentials, but nothing has an extension.
    – jmt333
    Apr 7, 2022 at 16:52
  • Very good answer, I would just add the same note about Transaction Log backups as you did about the Differential backups (the fact that they need the nearest previous Full backup to be functional). Also, up to you if you want to mention Veeam as an example common software that lives outside the database server instance and handles database and OS level backups.
    – J.D.
    Apr 7, 2022 at 21:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.