Multiple Transaction Log Files

Question

Multiple Transaction Log Files

Viewing 5 posts - 31 through 34 (of 34 total)

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

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 1

Simon-413722 (7/1/2008)
Whilst it does make sense to only have 1 transaction log per User DB, any idea about tempdb? The recommendation from M$ was to have 1 data file per CPU but they didn't mention anything about the number transaction log. I would assume 1 transaction log would suffice for multiple tempdb data files as well, but has anyone came across any credible evidence for this recommendation?
- Simon

You mean credible evidence that would be considered incontrovertible and admissible in a court of law? I don't think so.

Like most of us, I know what I read. I read that one log file for the TempDB database is all you need. I've never read anything else.

LC

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 2

GilaMonster (1/26/2012)
p.s. 3 year old thread.

And your point is?

If it's 3 years old, does it make the information in the thread any less valuable?

Being 3 years old, why did the link for it show up in my daily email from SSC?

LC

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

Lee Crain (1/26/2012)
GilaMonster (1/26/2012)
p.s. 3 year old thread.
And your point is?

That Jeff (or others) shouldn't waste his time re-starting a discussion that concluded 3 years ago. Often people don't look at the dates and spend lots of time on an issue that's been resolved and sorted for years.

Being 3 years old, why did the link for it show up in my daily email from SSC?

Because someone posted on it.

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

near2goal Valued Member Points: 57 More actions · Answer 4

Hi,

I inherited one database which have 8 log files placed in different drives, DB size is 2.5 TB and there is a log shrink job every night.

As the log files are sequential written and usually it requires only one log file and the reason for adding 2nd is to avoid any halt in the operations if 1st gets full (may be disk is full) then it would use 2nd log file.

To check if second and on ward files grow in the database I deployed 2 scripts,

first scripts is checking if any log file other than first start growing

if growth is detected then second script will check the disk space where first log is place.

Today is my 5th day in monitoring and I found that any disk never got full (even I asked SA to share the reports on the server especially for disks) but atleast 4 logs are growing (upto 30GB) ever day (after shrink job).

All log files have initial allocation of 5G with unlimited growth.

Any idea or reason for growth of n log files when there is no disk space issue.

paulfyi SSC Enthusiast Points: 198 More actions · Answer 5

You should allocate sufficient space required for the work load. Shrinking the files every night just to have them auto grow every day, is a waste of cpu cycles. If it requires 30 gb, allocate more than that and leave it. Setup an alert if the files begin to auto grow beyond that based upon how much space is available on disk.