2

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?

  • What is your RPO for the database? If being able to restore to the latest full backup is all you need then put the database in Simple Recovery Mode and SQL will manage the transaction logs itself. – joeqwerty Mar 12 '14 at 12:59
  • Just want to clarify that you probably mean hosting provider and not ISP, right? – Rex Mar 12 '14 at 17:21
  • @rex yes, hosting provider – Tim Long Mar 12 '14 at 18:59
3

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.

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:

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.

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.

  • 1
    A hosting provider providing an SQL Server database should know all this stuff already. Whilst not quote SQL Server 101 it's pretty damn close. – Mark Henderson Mar 12 '14 at 22:31
1

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.

0

Confused by the last sentence. It seems to imply that they are not backing up the logs,

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.

  • Taking a full backup truncates the log automatically, does it not? Manually truncating a log of a SQL Server database that is in full recovery mode will cause you to lose the log chain. Or am I missing something? – Chris McKeown Mar 12 '14 at 12:32
  • 1
    I still have issues with your answer here - you say that there is no reason to keep the full log after a full backup, but if they intend to use log backup to allow point-in-time restores then the transaction log should never be manually truncated. – Chris McKeown Mar 12 '14 at 13:23
  • 1
    There are a lot of discussions/articles/forum topics over the internet according this point. And the conclusion is: Taking a log backup will truncate the log, but taking a full database backup will not truncate the log So truncating logs after full backup it's just administrative procedure (care for disk space, etc) and not affects data integrity (as full database backup already done) – Sergey Mar 12 '14 at 14:21
  • 1
    @Sergey you need to be clear what you mean by 'truncating the logs after full backup'. If you mean BACKUP LOG WITH TRUNCATE ONLY then this WILL break the log chain, even if done immediately after a full backup. Just for the sake of people reading this thread, we need to make a clear distinction between 'backing up the log' (which also truncates) and 'truncating the log using TRUNCATE_ONLY' which breaks the log chain. – Chris McKeown Mar 12 '14 at 14:58
  • 2
    @TimLong Then this is purely incompetence. I come from a world where we backup the tx log every 10 minutes and THAT is in the 500-800mb range (for 10 minutes). If it takes several weeks - what "point in time" do they think to support there? If they take backups they should start releasing in a relevant timeframe. Pure bad setup. – TomTom Mar 12 '14 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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