Log File HUGE!! - 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

General SQL Server Forums

New to SQL Server Programming

Log File HUGE!!

Author

Topic

jrice
Starting Member

4 Posts

Posted - 2007-10-30 : 08:51:24

Hi guys, its my first post! Its also like my first time really diving into sql. We are using sharepoint on site here along with sql server 2005, one of our log files is 255 GBs and needs to be made smaller very fast!! We are almost out of disk space and the log is growing fast.

I am very new to sql and dont even know where to go to enter commands, so youll have to bear with me here.

I've read about truncating and shrinking and some other things, I am just worried and dont want to mess anything up. I know this is probably a simple task, but like I said, with the truncate command I was reading about, I dont even know where to go to type it in!!! If someone could please help it would be much appreciated. Thanks so much.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-30 : 09:07:27

See
http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.html

You can use dbcc loginfo to see the active log pages.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrice
Starting Member

4 Posts

Posted - 2007-10-30 : 09:22:09

That will be very helpful, thank you.

But... I cant seem to find enterprise manager, is this a different name in 2005?

jrice
Starting Member

4 Posts

Posted - 2007-10-30 : 09:30:48

Sorry, maybe if I did a little research before posting silly questions. I found it

So you are saying if i follow the direction on this site, hopefully it will shrink my log file? And Should I keep the recovery mode as Simple?

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-30 : 09:56:33

quote:
Originally posted by jrice

Sorry, maybe if I did a little research before posting silly questions. I found it

So you are saying if i follow the direction on this site, hopefully it will shrink my log file? And Should I keep the recovery mode as Simple?

You should keep recovery mode simple unless you want to be able to do point in time recovery. If not you should use Simple with frequent full or differential backups. If you want point in time recovery you should use full recovery but make sure to backup the transaction log frequently, we do it every 10 minutes.

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 09:57:38

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Why%20is%20my%20LDF%20Log%20File%20so%20big

jrice
Starting Member

4 Posts

Posted - 2007-10-31 : 12:38:38

Ok, I went ahead and changed the mode to simple, and shrank the file last night. It successfully shrank to 500 kb. Very nice.

I dont know if any of you are familiar with sharepoint, but this morning users are having issues approving workflows where they have to enter their passwords. Its telling them unauthorized.

This change to the log file didnt have anything to do with this did it?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-31 : 13:29:52

>> This change to the log file didnt have anything to do with this did it?
I would be surprised but sharepoint often surprises me.

Did you also reboot? Maybe someone changed something which only takes effect on a restart, maybe an account has expired, maybe can't get some resource.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-01 : 06:28:35

try this statement after regular interval

dbcc shrinkfile(logfile name,1)

but ensure the backup of the logfile before the shrinking

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 06:52:53

"This change to the log file didnt have anything to do with this did it?"

Might have moved some index blocks around, such that some queries are now timing out? REINDEX would sort that out, but grow the file again?

" try this statement after regular interval
dbcc shrinkfile(logfile name,1)
but ensure the backup of the logfile before the shrinking"

Sorry, but I think that is really bad advice. At the very least you would need to take a full backup immediately after that process so that the backup chain is not broken.

Kristen

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-01 : 13:15:35

quote:
Originally posted by arorarahul.0688

try this statement after regular interval

dbcc shrinkfile(logfile name,1)

but ensure the backup of the logfile before the shrinking

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

Well, you're staying consistent - still 100% wrong in your answers.

Read the other thousand or so posts here that explain why running dbcc shrinkfile is [b]not[b] a good idea.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-01 : 13:31:05

I have sent an email to arorarahul.0688.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-01 : 13:52:03

Well....

I'm in the middle of appying santized data into existing databases, and as much as I try to keep the logs from blowing up to be huge, they just do

So I have to shrink the logs to make the more manageable

Will the grow and cause overhead, perhaps, but it'll get back to a normal size in developement, and then they'll be fine

You just have to size it right

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 14:51:01

"Will the grow and cause overhead, perhaps, but it'll get back to a normal size in developement, and then they'll be fine"

You are aware of the consequences, and have a plan - which basically involves stopping Shrink and allowing the database to reach equilibrium once you are done with a bunch of heavily "one-off" stuff.

"Right-sized" - as you said!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-01 : 15:05:21

quote:
Originally posted by Kristen
and have a plan

Well, there's a first time for everything

I'm still looking for an option or method that will not load spaces padded when I bcp a fixed width file into a table

Drives my batty

EDIT: And I've never heard of a log that has grown to 255GB

What are you doing?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources