log files getting large - 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 2005 Forums

SQL Server Administration (2005)

log files getting large

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-03 : 01:56:36

my log file on my database is 304,313 mb
Would you do anything with it?
what is the purpose of the log file and is there anything to be done from time to time so it doesn't get too large.
It's much larger then my database.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-03 : 02:44:07

Are you running in full recovery mode?
If so, do you have regular log backups running?

--
Gail Shaw
SQL Server MVP

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-03 : 02:47:45

not sure how would i check that/
i have a scheduled database backup but I don't think log backup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 03:29:57

http://support.microsoft.com/kb/873235

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-03 : 08:23:30

Take a log backup and shrink the file with DBCC shrinkfile.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-07-03 : 09:46:02

And based on your other post(http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105903), if you're doing large deletes and index rebuilds AND you are in full recovery mode, you will either need to be doing log backups with some regularity or you run the potential risk of filling your disk.

Terry

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-04 : 02:53:59

If you don't care about been able to restore to the time of failure in the case of a disaster, set the database into simple recovery mode. If you do care about been able to restore to poin of failure, set up regular log backups.

--
Gail Shaw
SQL Server MVP

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-04 : 03:04:42

when trying to do a backup log i get
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

I assume this means i'm set to simple -- would this be the best way to leave it and does that mean that I don't need to truncate ge log?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-07-04 : 03:21:14

actually after just reading up on it - i just changed the recovery model to full.
I want to be able to restore in case of a problem and this is important to me.

in this case how often would you backup the log and shrink the file?
should i make this a scheduled job?
Please advise and thanks for your help

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-04 : 04:44:28

Backup the log, depends on how much activity you have and how large you want your log files to grow. Try once an hour, if the log files are too large, make it mor frequent. Yes, it should be a scheduled job

Shrink the log - never. Do it as a once off to get the size of the file to a manageable size, and leave it alone.

--
Gail Shaw
SQL Server MVP

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-04 : 20:09:21

How often to backup log depends on how much data you afford to lose.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources