What are best practices for setting transaction log size for tempdb?

Question

What are best practices for setting transaction log size for tempdb?

rajeshjaiswalraj

SSCrazy

Points: 2574
More actions
February 10, 2013 at 10:51 am

#394152

Please some one help me .....
:-)What are best practices for setting transaction log size for tempdb?

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

Rum a load test of your app, see how large TempDB gets. Set default size to slightly larger.

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

rajeshjaiswalraj (2/10/2013)
Please some one help me .....
:-)What are best practices for setting transaction log size for tempdb?

I wouldn't call it a "best practice" but here's what I do...

I set it to 100MB with 100MB Growth and then I watch it. Whatever it get's to after a full monthly cycle has passed is probably a pretty accurate size. I'll add half a GB to that and use that for the Initial Size keeping the 100MB growth. Of course, the new intial size won't kick in until the next bounce of the service or reboot of the server. I typically will do the same with the MDF file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

GilaMonster (2/10/2013)
Rum a load test of your app, see how large TempDB gets. Set default size to slightly larger.

Same thing I do. Other option is to run it for a while and monitor. Then adjust your settings.

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