Transaction log growing huge if DB is in simple recovery model

Question

Transaction log growing huge if DB is in simple recovery model

santhosh411

Ten Centuries

Points: 1155
More actions
June 27, 2013 at 4:48 am

#295085

Hi,
I have one DB and that DB is growing if the DB is in simple recovery model.
Please help me to resolve this issue and every time i am getting disk alerts.
Thanks,
Santosh

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

What is growing, the log file or the data file?

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

santhosh411 Ten Centuries Points: 1155 More actions · Answer 2

santhosh411

Ten Centuries

Points: 1155

June 27, 2013 at 4:53 am

#1627906

Log file

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

Please read through this: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

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

santhosh411 Ten Centuries Points: 1155 More actions · Answer 4

at present it showing nothing and recovery model is simple.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

You could shrink the LOG file when this has grown by a large one-time action. But if this (kind of) action is periodically recurring the LOG will grow back to the current size.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

girijesh.ipec SSC Enthusiast Points: 121 More actions · Answer 6

Shink Log Files and if it occurs frequently then automate Shrink file on Daily/weekly/Monthly depending on growth of log file size.

Regards,

Girijesh

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

girijesh.ipec (6/27/2013)
Shink Log Files and if it occurs frequently then automate Shrink file on Daily/weekly/Monthly depending on growth of log file size.

No, no, no! That is terrible advice.

If the lg file is growing there's a reason. The solution is to identify the cause and resolve. The log file should never be automatically shrunk (neither should the data files)

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

choodye Valued Member Points: 73 More actions · Answer 8

Gale Shaw is correct!

No offense to those who posted it, but the advice to schedule a shrink is bad advice. It may alleviate the symptoms. But it won't resolve the problem.

Most likely, there is a process that performs a huge select, insert, or update. If it happens daily, then you should easily be able to deduce what time and then narrow it down from there and turn on a trace when you have a window, as to when it occurs. then it will take a little detective work to determine the process that is causing the growth.

If a lot or or a large transaction is occurring during your backup, that would also cause the transaction log to grow as the DB must have somewhere to hold the records until the backup completes.

Ceddy

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

GilaMonster (6/27/2013)
girijesh.ipec (6/27/2013)
Shink Log Files and if it occurs frequently then automate Shrink file on Daily/weekly/Monthly depending on growth of log file size.
No, no, no! That is terrible advice.
If the lg file is growing there's a reason. The solution is to identify the cause and resolve. The log file should never be automatically shrunk (neither should the data files)

+1000.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)