tbragsda
asked on
How large is the largest ldf you have ever seen?
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
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
ASKER
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.
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.
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
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
ASKER
No... Just asking what is the largest in size, and as a percentage YOU have seen.
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..
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
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
ASKER
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
Thanks
TBR
NP
If my size example can help you -> it will be fine with me
Good night
If my size example can help you -> it will be fine with me
Good night
ASKER
Yup it does. I personaly have never seen 150+ log. You really had it as ONE ldf?
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
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
ASKER
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
Thanks
TBR
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%
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%
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%
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%
ASKER
Thanks all... How about normal groth vs. highest? Like if the one example of a 69g log, what is its growth normally?
TBR
TBR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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\cod
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