I received a support ticket today from my hosting provider (they created it), saying that my SQL transaction log had filled up and that they had truncated it, shrunk it and reset it to Full recovery model.
I'm confused as to why they let the transaction logs fill up to the point where a web site falls over and needs manual intervention. I asked them if they were backing up the databases and they replied (in slightly broken English)
We back up every day and keep the backups for a week. The transaction log has to be kept because we don't know what transaction you make and it might contain important information. The backup itself has the log truncated.
Confused by the last sentence. It seems to imply that they are not backing up the logs, which I guess is OK if they are running a full backup every day, but then if that is the case, why aren't the transaction logs getting truncated every day?
I tried to politely suggest that they probably should be truncating the logs, but I'm not sure they properly understand how the logs work. I can hold my own with SQL Server but I'm not an expert and I'm not sure enough of myself to call them on it. Also I don't know what backup software they are using or how it is configured.
So, are they justified in never truncating the logs? Is there any scenario where that helps? They seem to have a system whereby they get an alert when the log is full and they go in and manually run a truncate script. It works but it is inelegant, and means my web site falls over every few weeks until they notice the problem and fix it, at which point they delete the log which they told me earlier I needed to keep. Arghghghgh!
What would you do, oh SQL Server expert?
Yeah, that's not good.
Assuming your database is in full recovery (because, as Chris McKeown points out, if it was simple it would auto-truncate), here's what happens:
When you/they run a full backup, it includes the current state of the database at that moment. It does not truncate the log.
When you/they run a log backup, it backs up the transactions and, assuming a checkpoint has occurred, truncates (not shrinks) the log, making room for more transactions. So the transaction log should find a more-or-less stable size and stay there, barring odd behavior or your logs not being backed up often enough.
They said:
Um. Yeah. I am not filled with confidence by this.
I would ask them to clarify what kind of backups they're running daily: whether they're running full, differential, or log backups. If they're running nightly logs and never running a full, well, by throwing away last week they've broken their restore chain and will never be able to restore in case of failure. They've also, as Chris McKeown points out, broken their restore chain by truncating the logs.
I can't say for certain based on the information provided, but it certainly sounds like they're not running log backups at all. If they are, nightly backups aren't cutting it for you and the log needs to be backed up more frequently.
I also don't know what the Service Level Agreement for SQL restores is with your hosting contract, but you might want to revisit that with an eye to whether or not they're in compliance based on this information.
If the database is using the FULL recovery model and the transaction log is constantly growing, this implies that they are indeed not taking regular transaction log backups.
Is this a problem? That depends on what expectation your ISP are giving you about the recoverability in the event of a disaster. If they say that they can restore to a point in time between full backups then they're currently lying. Manually truncating the logs will break the log backup chain and make a point-in-time restore impossible.
If they're only guaranteeing recoverability to the last full backup then they should just switch to SIMPLE recovery mode and then the logs will get truncated automatically.
THat is ok. It is standard to truncate the log WHEN YOU TAKE A FULL BACKUP, as then you have the backup of the data in that moment.
Basically they rely on daily full backups and the local tx log for the time being - which in this case is simply not large enough. They should then expand the log (and you pay for it) or move to log backups at regular intervals (15 minute, hourly).
If they do not truncate the log after a full backup they are - "not smart". There is no reason to keep the full log after taking a full backup. But that is not the case - as they say: "The backup itself has the log truncated".
What happened is that you used more space for a day log than they have allocated. Likely you outgrow their low usage setup.