Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-21 : 09:16:16
|
| Michael writes "Why does my transaction log grow so large that I need to truncate it once a week? Shouldn't SQL Server 2000 manage this automatically?I have a database that is around 2100 MB in size with a transaction log when truncated is as small as 15 MB. Each day, the Tlog grows at a rate of about 300 MB +/- and then once a week when the optimizer runs it grows significantly (often larger than the data).I have two maintenance plans running, one for the database and one for the Tlog. The Tlog maintenance plan doesn't do anything to the database and it runs every day at 5:15pm. The database maintenance plan does a full backup once a day in the morning, verifies the integrity of the backup upon completion. In addition it also:-reorganizes the data and index pages (10% free space per page)-Once a week it removes unused space, shrinks database when it grows beyond 50 MB, with 10% available after shrink-Once a week it checks the integrity (excluding indexes)I have other databases that are comparable in size that do not have the same problem so I assume it is a setting in one of the maintenance plans.Can you offer any suggestions? Am I suppose to truncate every week? Is this normal?" |
|
|
Jay99
468 Posts |
Posted - 2002-03-21 : 09:48:03
|
| If you are not in a 'Simple' recovery model, your database logs transactions. If you have set 'Automatically grow file' for your Transaction Log, then if the logging requires more space, it will ask the OS to allocate it.Here is my suggestion. First, do some reading about SQL Server Architecture. Make sure you really understand, at a pretty low level, what happens when you issue a DML statement. Second, re-write your own ‘Database Maintenance Plans’ in the form of Jobs that issue backup statements, dbcc statements etc. Do this so that you truly understand what is going on.I guarantee (and I don’t usually do that sort of thing) that if you do the above two thing properly, you will not only find the answer to your question, but you will grow as a database professional, thus making your resume more valuable.Jay<O> |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-03-21 : 17:08:27
|
Now that is some good advice!Isn't that an old proverb? Give a SQL DBA a maintanence plan, you've helped him administer for a day. Teach him how to write his own maintanence plans, and you've helped him administer for the rest of his life.You a wise man Jay99  -Chad |
|
|
|
|
|
|
|
|
Excessive Transaction Log Growth