February 9, 2009 at 1:31 pm
All,
Recently I have encountered a situation where the log file a database approximately 80GB is suddenly reaching toward 150 GB after a weekly maintenance plan is run with reorganize.
I also run full db backups Mon-Fri night.
For the last year I have run a maintenance plan every Friday night using reorganize on one particular database. Typically I would find this causing the tlog to grow to nearly the same size as the data files, approximately 80GB. When I would come into work on Monday I would simple run a job to shrink the tlog back to 500MB. This scenario has been fine since the drive where the log file resides has a 150GB capacity.
However just recently the log file is now reaching 150GB after the maintenance plan using reorganize is run.
I do know that our software developer added tables recently for a new module in our business application. The total amount of new data is not great at all, our datafiles total 90GB. Other than that change I cannot think of any changes to have occurred.
Is there a way to find out what could be causing the recent spike in log file growth once reorganize is run?
Also what recommendations would anyone like to make when it comes to running reorganization in a maintenance plan for a targeted database?
Is there any trick I can employ which would reduce the tlog growth?
Thank you for your suggestions.
K
February 9, 2009 at 1:46 pm
Backup the log more frequently!
* Noel
April 6, 2010 at 11:44 pm
hallo
can you tell me how long your reorganize task runs ?
we have allways a database with 90 gb.
thank you
regards gg
April 7, 2010 at 1:13 am
kwoznica (2/9/2009)
All,Is there a way to find out what could be causing the recent spike in log file growth once reorganize is run?
I recommend you read the following article.
http://www.sqlservercentral.com/articles/Log+growth/69476/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2010 at 1:14 am
kwoznica (2/9/2009)
Also what recommendations would anyone like to make when it comes to running reorganization in a maintenance plan for a targeted database?Is there any trick I can employ which would reduce the tlog growth?
Thank you for your suggestions.
K
Backup the logs more frequently.
I would also look into breaking out the reorganize job to a staged type of process. Process x tables on day 1 and then x more tables on day 2 type of thing.
Does your tlog grow at all other than while performing the reorg?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply