Will performance effects if Transaction log file is big ?

Question

Will performance effects if Transaction log file is big ?

diva.mayas

Mr or Mrs. 500

Points: 569
More actions
May 4, 2010 at 1:29 am

#232248

Will performance effects if Transaction log file is big enough and overall transaction is moderate ?
If it effects the performance the need to know the reason behind it

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

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

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

A large log file is not going to cause performance problems by itself.

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

diva.mayas Mr or Mrs. 500 Points: 569 More actions · Answer 2

Thanks,

How can we create an optimistic maintenance plan for the below mentioned scenario -

1. i do have one 1 TB of Database size

2. 500 GB of Transaction log file

3. my database is in replication mode with 7 publications and approx. 50 Subscription

4. Accessing Concurrent users 400 users

5. Transaction occurs 400 /sec

6. Batches 400 / sec

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

Not enough information. What you've left out, and what is critical, is the allowable data loss in the case of a disaster. Need that to tell how far apart the log backups can be. You also need to consider what's the max allowable time for a restore, should one be necessary. Without those, it's impossible to even begin to suggest a maintenance plan.

If you have no idea where to start, and this is an important or mission-critical database, consider getting a consultant in to advise you.

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

diva.mayas Mr or Mrs. 500 Points: 569 More actions · Answer 4

How can we create an optimistic maintenance plan for the below mentioned scenario - ?

1. i do have one 1 TB of Database size

2. 500 GB of Transaction log file

3. my database is in replication mode with 7 publications and approx. 50 Subscription

4. Accessing Concurrent users 400 users

5. Transaction occurs 400 /sec

6. Batches 400 / sec

-----------------------------------

# what is critical, is the allowable data loss in the case of a disaster ?

its 0% data loss in case of disaster

# How far the log backups can be ?

its every 15 mins

# what's the max allowable time for a restore ?

Max allowable time for restore MAX = 1 hr

# Indexed size = 250 gb index

Thanks

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

diva.mayas (5/6/2010)
# what is critical, is the allowable data loss in the case of a disaster ?
its 0% data loss in case of disaster

Not possible just using backups. For 0 data loss, you need some form of high availability as well, a redundant, up to date copy of the database elsewhere

# How far the log backups can be ?
its every 15 mins

Then you're looking at 15 min data loss in the case of a disaster, unless you have DB mirroring or SAN mirroring or something similar

# what's the max allowable time for a restore ?
Max allowable time for restore MAX = 1 hr

How long does a full backup take to restore? I'll be impressed if it's under 1 hour with a 1 TB database...

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: 736280 More actions · Answer 6

If this is important, and it's worth money to your business to avoid data loss, or much data loss, hire someone. Building a DR solution is something that takes some thought, some experience, and some knowledge. Ask your boss what it's worth, and then hire someone to set it up, or teach you what can be done with your equipment.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

In addition to DR, what is your Business Continuity plan?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Dan.Humphries SSChampion Points: 12125 More actions · Answer 8

If the 500 GB log file is a result of alot of autogrowth then performance could be impacted.

I would recommend reading the following article.

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.