SQL Data and Log Files

Question

SQL Data and Log Files

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the max size required by your tran log through the day?

How often are the log backups?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

I prefer to say that the log should not be truncated at all.

That's not practical in this type of situation.

If the log is already bloated because it previously wasn't backed up often enough, the only way now to get the log back to a reasonable size is to shrink it. A log backup will mark committed parts of the log as reuseable, but of course it will not free the disk space; only a log shrink will do that.

So:

1) Truncate the log. [BACKUP LOG ... WITH TRUNCATE_ONLY]

2) Shrink the log file. [DBCC SHRINKFILE(...)]

3) Take a full db backup.

4) Put in place frequent enough log backups to try to insure the log does not get bloated again.

I've seen logs for 500M databases that were 67G ... it would be a mistake to have a "rule" that says I can't shrink that log back down to a reasonable size.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ScottPletcher (12/3/2008)
I prefer to say that the log should not be truncated at all.
That's not practical in this type of situation.
If the log is already bloated because it previously wasn't backed up often enough, the only way now to get the log back to a reasonable size is to shrink it. A log backup will mark committed parts of the log as reuseable, but of course it will not free the disk space; only a log shrink will do that.

A log backup will mark exactly the same parts of the log reusable as backing the log up with truncate only. The only difference between the two is that the first writes the log records to a file before it discards them, the second doesn't.

I never said don't shrink an overly large transaction log. I said that I prefer to recommend that the log not be truncated.

There are always exceptional cases, but they are just that, exceptions. What I'm mainly 'ranting' about if the knee-jerk reaction that seems to be popular - the log is getting large, truncate and shrink it, without asking why the log is large and it it really needs to be that large or not

Note that your option won't work on SQL 2008. Backup log .. with truncate only is deprecated on SQL 2005 and was removed in SQL 2008.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Conan Whalen-McKain-370433 Ten Centuries Points: 1154 More actions · Answer 4

Also review what your backup commitment is. Full backup with transaction logs are best but it may be that your backup commitment has to be much less and you should look at going from a full backup to a simple. That is part of determining what is needed be aware replication and other pieces of sql server only work with full backup model.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Replication can work in simple recovery. It's mirroring and log shipping that require full only (for mirroring) or full or bulk-logged (I think for log shipping). I seem to recall there's a 2008 feature that requires full, but I can't remember if it is the case, and which one it is.

Replication has its own markers in the tran log and the log records can't be discarded (by checkpoint in simple or log backup in full/bulk logged) until the log record is both inactive and replicated.

Change Data Capture in 2008 works the same way.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 6

There are definitely times to truncate and shrink the log, but these shouldn't be the first things you do, and you should understand the implications of doing so. Recommending these, without explaining why you don't want to do this regularly, is a poor practice, IMHO. In this case, it may be needed, but you don't want people reaching for this in the front of their tool boxes.

Definitely be aware of where you might need the full recovery model. Replication isn't a such a place, AFAIK. But this should be the default model unless you have a good reason not to use the full mode.