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?