?
Solved

How large is the largest ldf you have ever seen?

Posted on 2007-08-01
17
Medium Priority
?
279 Views
Last Modified: 2011-09-20
What is the largest MS-SQL transaction log you have seen?  As a percentage of the total database size, and total size?

I'm arguing with some of my developers about not only WHY the logs are growing, but what is unusual.

So... How do divide  points.  Well, I will give points to document largest total, and as a percentage of DB, OK?

TBR
0
Comment
Question by:tbragsda
  • 8
  • 6
15 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613676

 you can get it as big as your size of drive where it is
it can be ~150% -200%of datafile
---

there is not doc -> it is up to your environment\applicatio\code etc.

http://www.sqlservercentral.com/columnists/bknight/sizingadatabase.asp
Run trans log  backup (if in full Recovery Mode), truncate trans log, etc

also check
SQL Server 2005 Books Online
Factors That Keep Log Records Active

http://msdn2.microsoft.com/en-us/library/ms345414.aspx
0
 
LVL 1

Author Comment

by:tbragsda
ID: 19613689
Well... thanks, but Im not asking how to reduce, I can reduce the logs fine.  Im asking what the largest you have seen in production.

I really don't want to disclose the disagreement yet, but I will say I have one DB where the two ldf have grown more than 12g each over night.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613735
are you asking why?

If you run reindex -> it is logged operation -> delete indexes and create -> calculate size (s) all indexes ...

see
Transaction Log Guidelines
http://www.dbazine.com/sql/sql-articles/mullins-sqlserver
----
take for e.g temp db :
it canbe big as your drive can let it be
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:tbragsda
ID: 19613762
No...  Just asking what is the largest in size, and as a percentage YOU have seen.

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613779
I have seen ~150GB log size vs ~100GB data file =~150% bigger than data file: after unsuccessful nightly reindex  (drive was ~0MB), reinex job failed, etc..
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613831
try using sizer from HP http://h71019.www7.hp.com/ActiveAnswers/cache/70728-0-0-0-121.html
and you will get idea based on HDD for your environment what % HDD for log vs, data files of database.

tell me more:
I'm arguing ... but what is unusual?

Points: I do not care- you can keep them
0
 
LVL 1

Author Comment

by:tbragsda
ID: 19613872
Thanks for the SIZE post.  I really don't need help with any suggestions, but thanks.  Its a academic question.  How large etc.

Thanks
TBR
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19613965
NP

If my size example can help you -> it will be fine with me
Good night
0
 
LVL 1

Author Comment

by:tbragsda
ID: 19614004
Yup it does.  I personaly have never seen 150+ log.  You really had it as ONE ldf?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19614044
on tempdb as one and many (sql server 2005 CPU=number tempdb log as per MS )
on user db as one

Gota go -> have good one

try the Hp.com link above "sizer" - as idea <->"doc" to show your developers result -


HP Integrity server transaction processing sizer for Microsoft SQL Server 2005 (64-bit)
 Updated: 2005-11-07
Reviewed: 2005-11-07
 
 
 A quick and easy way to size your database server based on your user requirements. HP has developed this unique sizer/configurator to assist our customers in deploying Microsoft SQL Server.
 
 

 
  » HP Transaction Processing Storage Planning Calculator for Microsoft SQL Server
 
 
0
 
LVL 1

Author Comment

by:tbragsda
ID: 19614142
Again, not trying to size a server or anything, trying to make a argument.  I will let you in on part, my feeling is that the way the querys are wrong.  A large select should NOT result in log file growth of 20+g in 5 hrs in a db that under normal operation grows logs at ~2 g per day.

Thanks
TBR
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19614564
I have seen, in production, for a 2MB database, a 400GB log file (was there as a consultant to check why the heck a 2MB database was so slow :-)
problem was, there was no backup at all on that database, so that might not count :-)
so, the percentage is: 200000%

in current production, we have some 2GB databases with a printing job that blew the log up to 5GB in the beginning.
-> mainly because of unoptimized queries, updates etc.
makes 250%

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19615829
real life example:
Today 8/2/07 6AM :
after all nightly jobs (including maint; data manipulation, etc)
-------------------------------------
info for  1 largest db:
Database X:
3 trans log files- ~83GB total
1 mdf file   \
                  ----> total 4 files size-> 541GB
3 ndf files/

--------------trans logs are 15% of data files sizes
-----------

another example:
03/30/2007

Y database:
1 ldf file - ~69GB
3 data files- - total ~  65GB

as you see log is  106%
0
 
LVL 1

Author Comment

by:tbragsda
ID: 19618939
Thanks all...  How about normal groth vs. highest?  Like if the one example of a 69g log, what is its growth normally?

TBR
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1000 total points
ID: 19619503
there are  stats for the ldf file for 1 year (mothly max size):
GB             YYYYMM
--------------------------

3      200608
3      200609
3      200610
2      200611
3      200612
2      200701
2      200702
68      200703
36      200704
6      200705
9      200706
6      200707
6      200708
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question