Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What happens if the Log file properties Automatically grow box is unchecked and the log file runs out of space?

Posted on 2007-11-19
5
Medium Priority
?
317 Views
Last Modified: 2008-02-01
Hi,
We just recently found that a database which only needs a simple recovery model had been using a full recovery model and we shrank the log file from 6Gig to 11MB.  Very good news, and useful to know.  A couple of other questions - though - so I don't become overconfident.
1) what information does the log file contain for a simple recovery model that is used for a database that is backed up every night?  A process each day "refreshes" the records in the database - deletes all today and imports and adds all of the next day.  I would assume that this daily activity adds info to the log file - doesn't it?  
2) we used the DBCC Shrinkfile command to shrink the size of the log file to 10MB.  That was not scientific, just a guess - and then we unchecked the box that says "Automatically Grow File" - was that wise or not?  What is the potential impact of having that box unchecked?  Could that cause our daily "refresh" to fail?  Would it just stop importing rows and we'd never know the difference?

thank you.
0
Comment
Question by:wendelina
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20314782
you probably want to let it grow larger than 10 mb, some action queries can ecompass alot of growth, simple recovery with shrink the log for you if need be.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20314827
>1) what information does the log file contain for a simple recovery model
the same as for full recovery model. the "only" difference is that in simply recovery mode, the log entries are automatically marked as "overwritable". in full recovery mode, a log entry will only made overwritable upon transaction log backup (or full backup)

>I would assume that this daily activity adds info to the log file - doesn't it?  
yes

>2) we used the DBCC Shrinkfile command to shrink the size of the log file to 10MB.  That was not scientific,
indeed, but not important. the log file will grow eventually grow during that daily import, but once it has grown to the size needed, it will not grow again (of course assuming that the database remains in simple recovery mode).

> Could that cause our daily "refresh" to fail?  
it is possible, if the disk system, upon request of growing the file, could not fullfil that request fast enough.
however, that can only happen if the file is already quite large, the disk system too busy with other requests etc ...


0
 

Author Comment

by:wendelina
ID: 20314885
And would that be accomplished using the auto-shrink setting in the options tab?
0
 

Author Comment

by:wendelina
ID: 20314933
I guess simply put - should I REcheck that option to Automatically Grow the Log file - to avoid any future "gotchas"?  I certainly don't want to swing from one extreme (huge log that grows forever) to the other (tiny log that isn't large enough to sustain the daily log activity).
thanks
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20314986
>And would that be accomplished using the auto-shrink setting in the options tab?
leave that setting out. you should "never" use that, as it usually means less performance.



>should I REcheck that option to Automatically Grow the Log file
you should keep that checked on the log file... because if ever the log file cannot grow, any updateing change would be failing
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question