SQL Server Performance Forum – Threads Archive

Huge transaction log Causes performance problems

Hi i have a db with tr.log 4 times bigger than
db file.
I was told that this server has memory problem Of course i will monitor memory and truncate transaction log ,l
but can actually huge transaction log be a cause for the memory take over the server?

If the server is over stressed and if the activity against the database vast then everytime writing it to transaction log (with such a size) will consume most of the resources on SQL. You can find more information from Memory, physical disk and process counters using PERFMON. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

if the defaults were originally accepted at installation and never changed, the log file has grown in small increments. that is very stressful to server performance. one of the largest negavites is page splits. i have walked into servers with similar problems. it might be a good idea ( if you have a window for downtime ) to run the DBCC shrinkfile utilities against the large logfile. If the default of the logfile to grow at 10% is there i’d change that too. Let in grow in MB and choose an apprpopriate growth size. you have to have exclusive access to the db to run the DBCC shrinkfile utility. you’ll have change the recovery model to shrink the log file way down. BOL has good explainations. I had to use the utility 2x. once to shrink it to a particular size then "truncate_only" to free unused space to the OS. Good luck!

Detroit replied doing a truncate-only after shrinking…remember to do a full backup after truncate-only or you lose the ability to do point in time recovery until the next full backup is taken. Check BOL for ‘backup log’ and find ‘truncate-only’ gotchas…

You can use TRUNCATEONLY while using DBCC SHRINFILE itself which will take care of specified process. Ofcourse, ensure to maintain full backups at any time. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>