| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-05-09 : 17:44:48
|
| Sometime Back I have read from one of the participant thread regarding the growth sequence and maintenance of the LDF log file.In my case, I use to release the UNWANTED FREE SPACE from the LDF file from time to time based on the growth the DB Size.However, I have observed that Growth keeps on increasing on to the LDF location though the Primary MDF is consistent.Keeping in view of the Log file nature growth and I am not shrinking thereafter, but how to make it LDF to its minimum, as I completely believe that LDF will be the changed transactions and should write back to MDF once the CHECKPOINT issued.Now My question to patrons, how to keep the LDF location to its least?Do I need to issue the checkpoint regularly, if so, at what stage?If not Shrink OR Truncate (Which is not so best practise) how to keep the LDF not keep growing ?I could not understand what should be done for LDF, which is consistently growing irrespective of the transactions?Can anyone advise me. thanks all. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-09 : 17:54:39
|
| You are going down a very dangerous route that will affect performance negatively.To manage the transaction log, you should either regularly backup your transaction logs (such as every 15 minutes) OR switch your recovery model to SIMPLE. You are likely using FULL recovery model and not backing up your transaction log, hence the problem.You should not need to run CHECKPOINT as SQL Server does this for you.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-09 : 18:18:55
|
| Thanks TKizer as I was expecting especially from you.Nevertheless, I have 15 Minutes Tlog Backup going on all days between 6 AM till 9:30 PM and thereafter FULL backup between 10:30 PM. (DB's are in FULL recovery Mode) But LDF is regularly decreasing and consuming the disk space, How to control (We have increased the disk space in last 6 months to around 50 GB) yet it is showing the growth into LDF, and as advised - not to shrink and no issue of Checkpoint, then what should be the course to control the LDF?Thanks for your advise. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-09 : 18:34:09
|
quote:
Originally posted by pdset
How to control (We have increased the disk space in last 6 months to around 50 GB) yet it is showing the growth into LDF, and as advised - not to shrink and no issue of Checkpoint, then what should be the course to control the LDF?
Work out how big it needs to be for the transactional activity on your database, the frequency of log backups and the DB maintenance that you do and, once you have, leave it alone.May be worth a read - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-09 : 18:37:39
|
quote:
Originally posted by pdset
as I completely believe that LDF will be the changed transactions and should write back to MDF once the CHECKPOINT issued.
Not quite.The log contains the records of changes made to the database. SQL's IO design requires that the log records for a transaction are hardened in the log file before the data pages are written and before the transaction is considered complete.A checkpoint does not write anything from log to data file. A checkpoint writes all dirty pages to disk and, in simple recovery model only, marks inactive portions of the log file as available for reuse. A portion of the log is inactive if it contains no open transactions, no transactions that need replicating and no changes that have not been hardened into the data file.In full or bulk-logged recovery, a portion of the log file is only marked for reuse if it is inactive and has been backed up by a transaction log backup.--Gail ShawSQL Server MVP |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-09 : 18:48:03
|
| We have Tlogs till 9:30 PM and Full Backup at 11:30 PM also complete TLOG backup along with FULL Backup as well.We have DB's ranging between 2 GB - 176 GB and the LDF are almost close to the MDF sizes of all the DB's.So this is my problem as MDF and LDF files were very close to each other in size, so I am in contention for releasing the space from LDF location.Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-10 : 00:30:31
|
| That's True also I am also backing up the Transaction Log Backup during night hours.Sorry to pester the same question, despite doing all RIGHT Things still growing the LDF , then (without Shrink or Truncate) how to reduce LDF size.Thanks Again. |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-10 : 00:32:03
|
| Also, even if I do the Shrink, that will occupy next time with same size for LDF so no effect of Shrink and Truncate is ruled out since that's not the right solution.Please sugges. Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-10 : 02:31:09
|
| You have said two different things here, so I just want to check." I have 15 Minutes Tlog Backup going on all days between 6 AM till 9:30 PM"" I am also backing up the Transaction Log Backup during night hours"One problem I have seen is where Tlog backup is NOT being backed up frequently during the night when the Index Rebuild / update statistics / housekeeping runs. These can create large numbers of TLog transactions (often more than normal day-time activity), and you need to have very regular TLog backups otherwise the LDF file will have to grow.Look at your Tlog backup files and see what the size is at each time during teh day. Is there a time of the day when the Tlog backup files are much bigger? if so that is a time when lots of transactions are happening, and they may fill the LDF file and cause it to be expanded. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-10 : 12:43:29
|
quote:
Originally posted by pdset
Sorry to pester the same question, despite doing all RIGHT Things still growing the LDF , then (without Shrink or Truncate) how to reduce LDF size.
You manage its size via transaction log backups. Are you using a maintenance plan to rebuild the indexes? If so, then this could be your problem. You should instead use custom code that can intelligently decide what needs to be rebuilt and what doesn't. I have a feeling that your maintenance plan is rebuilding all indexes regardless of the fragmentation.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-10 : 21:36:06
|
| Yeah I have MP in place running weekly doing all Reindex.But to fix with SOME of the indexes only can be used with Custom Code but I need to FIND those indexes only, which is time taking.Any how thanks for it. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-10 : 21:43:39
|
| Run the following and post the results here:SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YOUR DATABASE NAME';If your log is continually growing, despite running transaction log backups - it is possible that you have replication set up but it is not working. You can also run the following in that database:DBCC OPENTRAN;This will tell you the oldest transaction in the database - which could also be preventing the log from being truncated. That could be caused by replication, or some other process holding a long running transaction. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-11 : 12:27:44
|
quote:
Originally posted by jeffw8713
Run the following and post the results here:SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YOUR DATABASE NAME';If your log is continually growing, despite running transaction log backups - it is possible that you have replication set up but it is not working. You can also run the following in that database:DBCC OPENTRAN;This will tell you the oldest transaction in the database - which could also be preventing the log from being truncated. That could be caused by replication, or some other process holding a long running transaction.
That's a great point. Database mirroring can also cause this. We have both replication (for reporting) and database mirroring (for DR) on some of our databases, so we have to be careful about our transaction logs. Any backlog on either could cause major problems.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-11 : 21:13:58
|
Run the following and post the results here:SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'YOUR DATABASE NAME';If your log is continually growing, despite running transaction log backups - it is possible that you have replication set up but it is not working. You can also run the following in that database:DBCC OPENTRAN;This will tell you the oldest transaction in the database - which could also be preventing the log from being truncated. That could be caused by replication, or some other process holding a long running transaction.Thanks Jeffw8713. There is no Replication running on any of the database on any server. still LDF hike is continuing...When I ran the Select... output was NOTHING was expecting something, but not. Surprising for me.What if, the Other transactions like Active_transaction OR Log_backup were shown then I would have some ground to investigate.Seems like some hidden play going one with me and interesting to chase.  You have said two different things here, so I just want to check." I have 15 Minutes Tlog Backup going on all days between 6 AM till 9:30 PM"" I am also backing up the Transaction Log Backup during night hours"One problem I have seen is where Tlog backup is NOT being backed up frequently during the night when the Index Rebuild / update statistics / housekeeping runs. These can create large numbers of TLog transactions (often more than normal day-time activity), and you need to have very regular TLog backups otherwise the LDF file will have to grow.Look at your Tlog backup files and see what the size is at each time during teh day. Is there a time of the day when the Tlog backup files are much bigger? if so that is a time when lots of transactions are happening, and they may fill the LDF file and cause it to be expanded.Thanks Kristen. you points are worth thinking from my perspective.Our Setup goes like 6 AM till 9:30 PM with a 15 min duration Log backup is (automated); followed by 11 PM FULL Backup along with that the days TLOG tansactions were COMPLETELY Backed up and any overnite transaction will be written in the first 15-min BACKUP OF 6 AM. In this way we backup whole database transactions.Weekly house keeping activities such as Reindex; recompile SP's; Update Statistics and CheckDB on Sunday morning where there is not much active transactions going on.As Gail Shaw predicted....I leave since the Transactions were placed onto Tlogs and there after backed up as my DB's were FULL Recovery Mode and Checkpoint business for SIMPLE recovery. So I leave the backups were now protected.The Changed transactions were placed onto LDF location(s) and there after a portion of LDF is kept open for next to come in. May be I need to work on what TKIZER predictions to work on those Rebuilding Indexes for only those Indexes have changed during that Week's time is another optionDespite THE PATRONS saying again and again.............My intention is to keep the LDF at its LOW, how this can be done is unknown for me............Thanks all who think on behalf of me and great source of learning here. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-11 : 22:39:58
|
| What is the result of running DBCC OPENTRAN in that database? |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-05-12 : 01:23:33
|
| RESULTNo active open transactions.DBCC execution completed. If DBCC printed error messages, contact your system administrator. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-12 : 03:14:29
|
| What are the sizes of TLog backup files during the day? At what time are the biggest ones?If housekeeping is Sunday morning only then you may have to check TLog backup after that in case that is the largest.You need to find WHEN the big TLog transaction backup is occurring. I think it unlikely that you have a stuck transaction, or somesuch, which is making your LDF file bigger." So this is my problem as MDF and LDF files were very close to each other in size, so I am in contention for releasing the space from LDF location"I work on the basis of expecting my LDF file to be up to 120% of my MDF file (my database is for an OLTP application), so it may not be possible for yours to get any smaller. However, if MDF file is not growing, and you do not have increase in user activity, then I agree that LDF should not be growing. |
|
|
|
Next Page
|
Log File Growth