SQL Server Performance Forum – Threads Archive

Transaction Log File Growth and Maintenance Plans

Hello, From what I have read, I think that the transaction log file for my database has grown large – more than 2GB, or slightly larger than the entire database itself – due to transactions logged during the nightly maintenance plan. Most of our hourly transaction log backups range from 5 – 20 MB in size, and then suddenly, in the hour after the nightly maintenance plan, the log backup is over 2 GB. These are the tasks in the maintenance plan, in the order in which they are performed:
ALTER INDEX REBUILD
BACKUP DATABASE
UPDATE STATISTICS
ALTER INDEX REORGANIZE
DBCC CHECKDB WITH NO_INFOMSGS I don’t recall where I read it, but I have read that the index maintenance tasks are the ones likely to cause large growth in the transaction log file. I have read advice that the solution to this problem is to switch the recovery model of the database to Simple just before the maintenance plan, and then change it back to Full just after the maintenance plan. Before setting up this type of job, the advice is to back up the transaction log and then shrink it to a size close to what it should be without the maintenance plan bloat. So, these are my questions. 1. Based on the information above, what is the likely cause of the log file growth, and how can I learn how to find out that cause? 2. Is switching recovery models for the maintenance plan an advisable approach? If not, is there some other best practice for managing the effect of maintenance plan transactions on the log file size? 3. Is there an existing script for switching recover models that I can modify and re-use, or some outline that someone can provide so I do the right things in the right order to keep the transaction log file size under control and avoid the maintenance plan bloat – but do so in a safe way? I don’t see any way in the GUI to switch recovery models as part of the maintenance plan wizard. Thanks in advance for any help or advice! jrdevdba

---

ALTER INDEX REBUILD and ALTER INDEX REORGANIZE cause to grow the log file…ALTER INDEX REORGANIZE takes more log space than REBUILD but why do you do both one REBUILD should be fine… If you want to change the recovery models check ALTER DATABASE syntax in BOL…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thanks for your help. Your question about why I included REORGANIZE and REBUILD led me to research the different options to learn more about them. The Microsoft SQL Server 2005 Administrator’s Companion (pp. 338-339) states: for ALTER INDEX REORGANIZE:
for ALTER INDEX REBUILD:
So I was using the options redundantly because I didn’t understand them enough. Now that I have done some research, I am thinking about separating the single maintenance plan into two maintenance plans: Plan 1 (Runs daily)
BACKUP DATABASE
UPDATE STATISTICS Plan 2 (Runs weekly)
DBCC CHECKDB
ALTER INDEX REBUILD I will work to script Plan 2 so that I can switch to Simple recovery before it and switch back to Full after it. Does that sound reasonable? Also, this link states that REORGANIZE is fully logged regardless of the recovery model chosen. Is that because it is a less intensive operation? At any rate, I am planning to go with REBUILD only, on a weekly basis. I think that will work. Thanks again for any additional advice,
jrdevdba

---

Yes, you can run two plan as mentioned… If you are using EE then you can rebuild with ONLINE ON option… REORGNIZE is online where as REBUILD is offline by default and it can be online if you have EE and table don’t have text/image columns…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

For the sake of recoverability you have to perform additional backup after this reorg & reindex process is finished. Otherwise during the failure after this process is finished you will lose the database transactions. Also better to run Transactin log backups during this operation without touching the database recovery model. http://msmvps.com/blogs/ssqa/archiv…p-2000-index-optimization-best-practices.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

Thanks! Regarding the transaction log backups you mention in the last line – do you mean to run a transaction log backup after doing the full backup for the sake of recoverability? – jrdevdba

---

I think you should do the update stats AFTER you do the REBUILD. ***********************
Dinakar Nethi
***********************
http://weblogs.sqlteam.com/dinakar/

---

Dinkar I think rebuild takes care of updatestats. I remember reading somewhere in SSP that we dont need to update statistics after rebuild because the rebuild takes care of it. Am I right? Satya

---

If you are rebuilding all the indexes then I don’t think you need to run the update stats …. MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

It was me always stress that point, if you have successful rebuild of index for that table then no need to perform update stats again for that table. Jrdevdba
Yes for the data consistency it is better to perform addition trans.log backup after the full backup. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>