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?

Rex
  • 7,895
  • 3
  • 29
  • 45
Tim Long
  • 1,738
  • 1
  • 21
  • 41
  • 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 Answers3

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.

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
  • 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.

Chris McKeown
  • 7,168
  • 1
  • 18
  • 26
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.

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • 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
  • I am not sure it is relly automatic (would have to check it), but it is how it is normally done. I basicalyl think they can ntot handle large tx volumes with a once daily only log backup ;) – TomTom Mar 12 '14 at 12:34
  • Manually truncating the log is definitely a no-no when it comes to the full recovery model. I'd check your info, but manually truncating will lose your ability to do a point-in-time restore – Chris McKeown Mar 12 '14 at 12:37
  • I personally think their backup model is simplistic (likely from people not knowing really what they do) and they should have no issue for example making an hourly log backup. – TomTom Mar 12 '14 at 12:56
  • 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
  • No. Once you take a full backup there is no "point in time" sensible anymore. Log backups are for the time between full backups. Basic dba knowlege - maybe you better read the documentation. – TomTom Mar 12 '14 at 13:27
  • `If they do not truncate the log after a full backup they are - "not smart"` - (manually) truncating the transaction log after a full backup will break the log chain and render the rest of the t-log backups useless! I think perhaps the way you've phrased it is a little ambiguous. t-logs should never be manually truncated - the act of taking a backup will truncate them. – Chris McKeown Mar 12 '14 at 13:31
  • [The log is truncated when you back up the transaction log](http://technet.microsoft.com/en-us/library/ms189085%28v=sql.105%29.aspx) – Chris McKeown Mar 12 '14 at 13:33
  • Yes. Basically the automated backup script * Takes a full backup and then * truncates the log so all backed up data is released from the log. Standard behavior. There is no need to keep the logs for those data that was in the last complete backup. THen during the day you do normally pure lock backups (that also release space) allowing point in time recovery UNTIL THE NEXT NON-LOG BACKUP. It should not be manual - it should be part of the automated scripts they are running. Sadly it seems they make no interim log backups and log space allocation is limited. – TomTom Mar 12 '14 at 13:34
  • I think we're getting our wires crossed here. When you refer to "truncating the log" what exactly do you mean? To me, manually truncating a t-log means issuing a `BACKUP` command `WITH TRUNCATE_ONLY` which will lose your log chain regardless of when you do it - even immediately after a full backup of the database itself. – Chris McKeown Mar 12 '14 at 13:40
  • 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
  • @TomTom it is taking several weeks or months for the Tx log to fill up – Tim Long Mar 12 '14 at 19:04
  • 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