Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Transaction Log Files


Multiple Transaction Log Files

Author
Message
Gail Wanabee
Gail Wanabee
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1306
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
Gail Wanabee
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 1306
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
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
near2goal
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 164
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
paulfyi
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 118
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search