asked
Scheduled Database Backup job fails due to lack of disk space
Two of my scheduled SQL backups failed over the weekend because of a lack of space. The C drive has 25.3 GB of free space out of 400 GB. The primary space taker is SQL. --149.9 GB C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP --139.7 GB C:\Program Files\Microsoft SQL Server\MSSQL\DATA Any suggestions as to how to fix this problem? Please help! I'm a rockie DBA confronted with this issue.
Before we can even go for a fix, we should know, whats out there in those folders. In backup folders, check for the .bak files, that means look for the OLD backups . Say per requirements, you may not be needing backups older than 3 days, so deleting old backups will help here. Now on the DATA folder check for the data files and see if there are transnational log files as well. First of all the .MDF should not be on the C drive , if that's here the case is. In case you have LDF's as well here, look for their growing size and need to separate them from data drive. Rest you need to make sure any unwanted files should not be sitting out there like SQL set up files or old archival data. Filter out the old/unwanted things, get them moved or archived and you should be good to go. Adding one more point, if you are using SQL server 2008 above, try to utilize the compress backup feature, which limits the size both of backup as well space occupied on disk. And for backups as well, it is suggested a different RAID level drive. Per my experience, depending upon you're environment get the drive allocated separately for Data, Log and Backups.
Backups should never be stored in the same location as your production database files. What will happen if the C: crashes on this server, you lose your databases and the backups you are taking to be able to restore from. First bit of advise is move these backups to another server or network share. Second bit of advice is what is the retention of the backups? People tend to want to keep at least two weeks in order to be able to go back in time if an event was not discovered for a few days or week, IE data deleted or updated incorrectly. If you have to long of retention you can delete some of those old files. Also what is the backup strategy? Nightly full, consider switching to weekly full and daily differential, also depending on version of SQL are you using backup compression. It is including in all versions since 2008 Enterprise. I would not advise a knee jerk reaction and deleting down to a few days of backups.
16 People are following this question.
