Transaction log questions

Question

Transaction log questions

Margaret Norkett

SSC Veteran

Points: 223
More actions
January 20, 2005 at 12:43 pm

#92114

I posted this same question on another forum I think incorrectly, so here goes.
My DB is 17G in size and my transaction log is 10G. Running SQL7.0 on this particular one.
Questions:
Should my transaction log be that big? This is a very active DB, so there are lots of transactions going on. We back the log up hourly and the entire DB (including the transaction log) daily.
When, if ever, should I back up the Transaction Log with No_log?
How would I determine if there is an old open transaction that is causing the log to grow?

Thanks in advance!

Margaret
Thanks and have a NICE day!,
Margaret

Viewing 10 posts - 1 through 9 (of 9 total)

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

hoo-t SSChampion Points: 12713 More actions · Answer 1

Whether your transaction log should be that big depends on a number of factors, including whether and how often you perform reindexing. As a general rule though, a smaller transaction log will provide better performance. With a very active DB, as you said yours is, transactions can accumulate and cause the log to grow quickly. You may want to consider doing even more frequent transaction log backups. We take transaction log backups every 15 minutes in most cases.

I ALWAYS hesitate to say NEVER, but... On a production transactional database, I would say that you should NEVER back up the transaction log with No_log or Truncate_Only. All this does is the same thing as a normal transaction log backup, except it throws the backup in the trash can. You lose your point in time recoverability, which is the whole purpose of taking transaction log backups in the first place.

Its possible, but unlikely, that an old open transaction is causing you grief. You can run DBCC OPENTRAN to see the oldest open transaction.

Steve

Margaret Norkett SSC Veteran Points: 223 More actions · Answer 2

Steve:

Thank you for your explanation and information. I did the DBCC OPENTRAN as suggested, but I'll be darned if I understand what it says

Transaction information for database 'openp'.

Replicated Transaction Information:

Oldest distributed LSN : (33193:1655:1)

Oldest non-distributed LSN : (0:0:0)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I'll change my transaction log backup job to doing this every 30 minutes and see if that makes any difference in the size of the log.

I'm feeling my way around in the dark

Thanks and have a NICE day!,

Margaret

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

If the log was that big before you started doing your backups then without doing a shrink it will stay that size and not grow anymore.

DBCC SQLPERF (LOGSPACE) will tell you how big the log file is and how much is currently being used. Reindexing large tables will cause your database log to grow to 1.5 times the size of the index being created.(fyi)

Tom

Margaret Norkett SSC Veteran Points: 223 More actions · Answer 4

So, if it says that my log file is using 1.06 % of the space should I truncate it?

We do reindex the larger tables as part of the nightly jobs.

dbname size(mb) space used(mb) status

openp 1693.7344 1.0629134 0

Thanks and have a NICE day!,

Margaret

noeld SSC Guru Points: 96590 More actions · Answer 5

So, if it says that my log file is using 1.06 % of the space should I truncate it?

My opinion is NO. Why? If you shrink it and is going to grow again because you will perform the same operations over and over you will be effectivelly fragmenting the file with shrinks and grows. The one time I would consider the shrink is when the space on your HD is tight ( very unlikely this days ) or if you performed a one time massive operation.

HTH

* Noel

hoo-t SSChampion Points: 12713 More actions · Answer 6

Margaret,

Also, realize that there is a difference between "shrinking" a log and "truncating" a log. Truncating removes the inactive transactions from the log, but does not reduce the physical size of the log. This happens EVERY time you back up the transaction log. If this is done outside of a log backup, your recovery chain will be broken.

Shrinking does NOT truncate the log. Instead, it tries to reduce the physical size of the log. I say tries because if the log is full, it won't shrink. This has no effect on the recovery chain, unless a truncate is done also.

You can think of it like this... You have a bucket full of water. You can truncate that bucket by tipping it and letting water out. If you want to shrink it, you must cut some off the top of the bucket (or just get a smaller bucket).

Finally, Noel gives good advice. Your log's physical size is probably what it is because of the reindexes, and it will need that space to be able to do it. You can verify this by looking at your transaction log backups. Find the largest one and compare that to the size of your transaction log. Should be fairly close. If your transaction log is a LOT bigger than your largest transaction log backup, then you can probably safely shrink the log to just bigger than that largest log backup.

Steve

SQLBill SSC Guru Points: 51440 More actions · Answer 7

Margaret,

I agree with Steve (hoo-t), the issue is your REINDEXing. When I do a DBCC REINDEX my t-log gets HUGE. Try this...turn off your REINDEX job. Shrink the log. Let it go two days. Does your t-log grow? Keep track of how much it does grow. Turn the REINDEX job back on. Compare the growth.

DBCC REINDEX needs a lot of t-log space in case the job fails or is cancelled. It needs to be able to rollback any work done.

-SQLBill

Margaret Norkett SSC Veteran Points: 223 More actions · Answer 8

Why do you reindex? Is there any advantage to doing it on a regular basis? How often would you recommend reindexing (if at all)?

I have some really huge tables (millions of records in them) and our process slowed to a crawl at one point, so we examined those big tables and decided the indicies were not optimal, dropped them, rebuilt them, then set the nightly job to reindexing daily. This boosted performance -- whether it was *just* the optimizing of the indicies or actually doing the reindex, I can't tell you as I was only peripherally involved at that point. However, from what you are telling me, perhaps the nightly rebuilding is not necessary and may actually be undesireable. Perhaps a monthly, quarterly or semi-annual rebuild would be just as effective??

Thanks! This is great information!

Margaret

Thanks and have a NICE day!,

Margaret

hoo-t SSChampion Points: 12713 More actions · Answer 9

Margaret,

It really depends on the insert/delete/update activity on the table(s). In particular, inserts and deletes cause index fragmentation. There's no harm in rebuilding the indexes every night if you have a maintenance window to do that. But, you're right, it may not be necessary. One thing to do is to save the results of a dbcc showcontig before each reindex, and possibly immediately after. Compare the scandensity value for the table(s) you are concerned about. The higher the value (closer to 100), the better. Ideally, immediately after a reindex, it should be really close to 100. If it falls off very much by the next night before the reindex, you are probably doing right by reindexing every day. If it doesn't, you might want to try every other day. Do this until you find the frequency that works for you. I have one database in particular that the entire database is reindexed every day. Others are scheduled much less frequently, and may only reindex a percentage of the database when it runs. Still others, I only reindex particular tables.

I have a procedure that provides a high level of control as to how reindexes are handled. It allows you to pass a parameter telling it to reindex the n% worst indexes. Any value can be used. It also allows you to choose a threshold scandensity. If 90, it won't consider reindexing any index that has a scandensity greater than or equal to 90. It saves the output of the dbcc showcontig in a SQL table for later review. Its a very powerful procedure for index maintenance, but can actually harm performance if you do not use it correctly. It does have fairly extensive documentation in the code explaining what it does and how to use it. If you would like to have it, please send me a private message and I'll be happy to send it to you.

Steve