Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction log growing huge if DB is in simple recovery model Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 4:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 5:46 AM
Points: 37, Visits: 239
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
Post #1468076
Posted Thursday, June 27, 2013 4:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
What is growing, the log file or the data file?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1468079
Posted Thursday, June 27, 2013 4:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 5:46 AM
Points: 37, Visits: 239
Log file
Post #1468080
Posted Thursday, June 27, 2013 4:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1468081
Posted Thursday, June 27, 2013 5:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 5:46 AM
Points: 37, Visits: 239
at present it showing nothing and recovery model is simple.

Post #1468086
Posted Thursday, June 27, 2013 5:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 2,310, Visits: 2,790
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’! **
Post #1468091
Posted Thursday, June 27, 2013 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 9:21 AM
Points: 25, Visits: 78
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
Post #1468269
Posted Thursday, June 27, 2013 11:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
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 2008, MVP
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

Post #1468277
Posted Friday, June 28, 2013 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:57 AM
Points: 1, Visits: 99
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
Post #1468704
Posted Friday, June 28, 2013 4:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1468727
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse