June 11, 2007 at 12:58 pm
One of the systems that we cut over to SQL 2005 a few months ago has one issue that I haven't solved. The log files (even after shrinking them) are gigantic. (>2.5GB on a DB that has a DB file of <2GB)
I am doing full backup nightly, and even truncating the logs daily, so I'm not quite sure what is causing the log file to be so large. This isn't the standard case of never truncating the logs and having them grow forever. They have been truncated daily, and they keep re-rowing daily.
Here is my daily maintenance routine after COB:
Since I know I don't know much about the various maintenance routines, my leading guess is that one of the above functions is adding massive amounts to the size of the T-logs. (Although I thought that Truncating the logs after the maintenance is performed would remove that info from the logs.)
I don't think that the size is related to any particular routine activity of the database. (The T-log size stays pretty consistent day to day, plus the production and test copies of the database have similar problems, even though Test is used much less than production.)
Any suggestions on reducing the size of the T-logs?
Warm Regards,Greg Wilsonsolidrockstable.com
June 11, 2007 at 2:24 pm
Reindexing causes the log file to grow approx 1.5 times the size of the database file. Why are you reorganizing AND rebuilding the indexes? Reorganizing is the same as defragging them and rebuilding the indexes is just that - drop and recreate the indexes brand new.
Truncating the logs, checkpoints and removes commited transactions but does NOT release the free space. Shrinkfile does that.
How often do you backup the transaction logs? If your database is in FULL recovery mode, you should do it frequently throughout the day - at least once an hour.
-SQLBill
June 11, 2007 at 2:31 pm
I'm doing them both because I don't know any better. What is a better solution? Only reorganize? Reorganize daily and reindex once a month?
Warm Regards,Greg Wilsonsolidrockstable.com
June 12, 2007 at 9:37 am
Depends. I know that is a wishy-washy answer, but it's true. We don't know your environment and/or requirements.
I had a 300 GB database (SS 2000) at my last job and it had to be available 24/7. Rebuilding the indexes was not an option since that locks users out of the tables while the index is being rebuilt. So I had to defrag the index (DBCC INDEXDEFRAG). Here at my new job, we have smaller databases and have 'downtime windows' where the databases are either not in use or have minimum usage. We do reindexing here as it does not affect the business.
So only you can determine which is best for you.
-SQLBill
June 14, 2007 at 6:22 am
Is there any real need to do your rebuild of the indexes and reindexing nightly? It is a process heavy routine. Would once a week work for your enviornment? The rebuild of the indexs should be done as a separate maint plan from the optimizations, reorgs and such per the BOL. Also I have found that I must do a weekly truncate in SQL2005 on the tran logs too, and if the reindexing/rebuild is not completed by the time the truncation routine runs no truncation is done.
June 15, 2007 at 5:07 am
Ideally REBUILD is best, but it has negatives. It does require 1.2 times of the space of the index's. It is transactional, so it is fully logged and locks the table. It also UPDATES STATS, a plus.
Look at your situation, as a nightly REBUILD may not be unncessary. Do you have a high # of page splits on a daily basis, if not, consider a weekly REBUILD.
You list that you REORGANIZE, then REBUILD. You would want to chose only 1 option.
REORG, will hold minimal locks, fill the pages, and can be stopped/restarted. It doesn't UPDATE STATS. Requires free log space.
These 2 steps alone will really grow the transaction log.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply