“Drives used for SQL databases are running out of free space”
And the disk space on that SQL server was of course full, so we had to first expand the disk with an extra 100 GB so that everything started working again. But that is only temporary panic solution 🙂 We also got these messages from the backup:
“Database XXX is configured to maintain transaction logs. Transaction log backups are not being performed. This will result in the log growing to fill all available disk space. Regular log backups should be scheduled or the database should be changed to the simple recovery mode.”
So I changed the Recovery model on the SQL db’s that were listed in that mail, from Full to Simple.
We run daily SQL backups so I believe that Simple recovery model is enough. If I need to restore a site (which is the most common restore scenario, since the Recycle bin was introduced in MOSS I have never had to make a restore for a single document). But I have at least a restore from one day back in history and that has never been a problem for us in restore scenarios. The difference between Full and Simple: to be able to go back to a specific time of failure, which can be done in Full mode.
Recommendation on Technet after switching to Simple recovery model:
Discontinue any scheduled jobs for backing up the transaction log.
Ensure periodic database backups are scheduled. Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.
I found a good explanation of Simple Recovery Model on MSSQL site:
The “Simple” recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the “Full” recovery model.
NOTE: The recommended model would of course be to have a Full recovery model, since you can go back to the single point of failure time, but for us the Simple mode has been enough.
I look forward to hear what others think of this recovery model and if you have other recommended settings that helps running out of disk space 🙂
