controlling log file growth - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

controlling log file growth

Author

Topic

sabuv
Starting Member

3 Posts

Posted - 2009-06-23 : 01:23:23

Hi there,

(I am very new to SQL administration and recently I have got the responsibility for maintaining 5 to 10 databases sitting on different servers)

I got a 35GB database,for which I do a full backup daily & a transaction log backup every 1 hour b/w 9.00am to 5.00 pm.

I have got some concerns about maintaining the size of the transaction log file.There is a database maintenance plan running on every sunday,to do the optimizations(default options like,reorganize data & index pages,change free space to 10%,and removes unused space as well).

My problems starts here!!!!!
After running the optimization job,my transaction log file grows to 25GB in size(.ldf file)
I know we can truncate/shrink the log file, but having read many articles,I got an impression that I shouldn't do that because it can cause fragmenation.

question1. Can I leave the log file as it is? (I got enough drive space)
OR
question2. Can I use some script to do the optimization jobs(like rebuilding index,statistics update etc) manually only for selected tables or indexes based on fragmented info? if that's the case,is there any problem if I remove my existing maintenance plan which basically does for all the tables?

your help would be much appreciated.

thanks,
Sabuv

arry.net
Starting Member

1 Post

Posted - 2009-06-26 : 06:27:45

I Too have this Problem.
and I deal with it by executing this query to truncate log filr:
trully i am using this from a long time and never faced any problem.

BACKUP LOG <Dbname> WITH TRUNCATE_ONLY

--db_log (logical name)(rightclick database and u'll find it under Files)
DBCC SHRINKFILE (db_log, 20)--in mb 20 mb

SELECT file_id, name
FROM sys.database_files;
DBCC SQLPERF(LOGSPACE);

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-26 : 07:00:40

If you're not short of space, leave the log alone. There's no problem with a large log file.
If you want to stop it growing so large, you can switch the DB into bulk-logged recovery. Index rebuilds are bulk-operations.

Just one thing. Remove the shrink database from your maintenance plans. Rebuilding indexes and shrinking straight after is essentially a zero-sum operation. You end up with lots of log activity and indexes that are more fragmented than they were before the rebuild.

Shrinking databases is a bad idea, end of story.
[url]http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/[/url]
[url]http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx[/url]

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-26 : 07:04:01

quote:
Originally posted by arry.net

BACKUP LOG <Dbname> WITH TRUNCATE_ONLY

--db_log (logical name)(rightclick database and u'll find it under Files)
DBCC SHRINKFILE (db_log, 20)--in mb 20 mb

Two major problems with that.
1) By truncating the log you have broken the log chain. Until another full backup is taken you will not be able to take log backups and you will not be able to do point-in-time recovery.
2) By shrinking the log right down you're essentially forcing it to immediately grow again. Log grows are expensive, the log cannot be instant initialised and hence repeated log grows will result in occasional periods of slower performance as the log file grows and operations wait for it to finish. Other problem is that repeated log shrink/grow will leave you with internal log fragmentation (which slows down backups) and external log fragmentation, which slows down most accesses to the file.

--
Gail Shaw
SQL Server MVP

sabuv
Starting Member

3 Posts

Posted - 2009-06-28 : 19:43:21

thank you Monster & Arry for your comments.

As Monster said, I am keeping my log file as it is.
And removed the maintenance plan (reorganizing & shrinking database)

what am doing now is,I have scheduled to run an index rebuild based on fragmented info.(Thanks for TARA's script)

I hope am doing the right thing Monster?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-29 : 07:59:38

Monster? That's a version of my nickname I haven't heard before.

You still need to reorganise data and index pages. It's just the 'change free space to...' option that you don't want. Though, if you're got a separate job that rebuild indexes (like Tara's one) you can remove that step as well.

--
Gail Shaw
SQL Server MVP

sabuv
Starting Member

3 Posts

Posted - 2009-06-29 : 21:14:59

Thanks.That's what exactly I am doing now Gail Shaw(Sorry if I am still misspelling your name)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources