database in simple mode with growing log file into the GBytes

Question

database in simple mode with growing log file into the GBytes

HildaJ

SSCarpal Tunnel

Points: 4516
More actions
September 4, 2013 at 7:23 am

#279426

I'd like to get suggestions on how to handle the issue I'm facing. I have a database that sole purpose is to be a staging place for data. Every night thousands of records are re-loaded to transfer new information and refresh the database. I have the database setup to simple mode however the log file grows up to close to 30GB due to the inserts and deletes from the batch scripts. I have a script that shrinks the database log file every day but since the reload is daily is grows up to 30GB again. I'm only responsible for the database maintenance not loading the data however, I'd like to give the people responsible for it a suggestion on how to do it better.
What's the best way to handle a process like this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

If you have the space available, keep the LOG at 30GB. Don't shrink the file.

This space is needed by the load, so daily shrinking is a waist of resources and hits overall performance of the LOG (read about LOG and VLF's http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/).

If you need to keep the size of the LOG smaller then the load has to be done in smaller chunks and/or more seperate transactions.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Steve Jones - SSC Editor SSC Guru Points: 734542 More actions · Answer 2

I'd listen to Hanshi.

The definition of insanity is doing the same thing over and over, but expecting something different.

Why shrink the space if you need it again soon? You don't have a growing log file, you have a log file that gets to the size it needs and you keep changing it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If the log needs to be 30GB to accommodate the daily activity (probably lots of inserts/deletes in single statements), then leave the log file at 30GB. It needs to be that size, why waste time shrinking it and waste more time and resources growing it again?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 4

I had a gut feeling that was going to be the answer but I'm relatively new DBA and wasn't sure.

Thank you for your feedback. I'll talk to my network guy and explain what's going on.

Thanks again.

michaelkinlb Ten Centuries Points: 1158 More actions · Answer 5

Suggest to your developers that they load in batches. This will help to keep your log file to a more reasonable size.

Regards.

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 6

Michael,

I'll take that in consideration, thank you so much for your suggestion.

Hilda