Solved

Database Log growth question

Posted on 2008-10-08
9
254 Views
Last Modified: 2012-05-05
When creating a new database using the New Database dialog window, there is an AUTOGROWTH option for the log file.

If I set a Restricted File Growth size of 20MB, what happens when the log file reaches its maximum size?  Do I start getting errors or does the log file start writing over itself?

Also, what would happen to an existing database log that has a current filesize of 40MB if I set its Restricted File Growth size to 20MB?
0
Comment
Question by:Rouchie
[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
  • 3
  • 3
  • 3
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 22668013
is your db in full recovery mode?
do you perform regular transaction log backup?

yes+yes => you should be safe, but still setting restricted growth is dangerous, as indeed whenever that size is reached, and more transactions are trying to write data there, they will fail.

yes+no => you have a problem anyhow, as you need regular t-log backup so that the log file space can be reused internally

no+xxx => similar to the yes+yes, as you might have many concurrent+large transactions requiring more than 20MB of transaction log space at the same time.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22668015
>Also, what would happen to an existing database log that has a current filesize of 40MB if I set its Restricted File Growth size to 20MB?

you cannot do that.
0
 
LVL 25

Author Comment

by:Rouchie
ID: 22668154
Hi AngelIII

The database is in Full Recovery mode.  The thing is I haven't yet done any transactional log backups because I'm still trying to understand it all.
Am I right in thinking that a transactional log backup removes the entries from the log file into a separate backup file?  Would that type of backup therefore shrink the log file?
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 22668164
The backup itself does not shrink the file.  To shrink the file, you would need to use dbcc shrinkfile.
0
 
LVL 25

Author Comment

by:Rouchie
ID: 22668237
Okay thanks for that.

If I backup the log, say, each month, then I presume the backup file filesize gets larger and larger?
So eventually there must come a point where older backups are not needed anymore.  Is that correct?  If so, can you just delete them from the backup or is that really risky?
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 22668253
IF you only backup the log once a month, you're not really solving any of your problems.  How often are you doing a full backup?  You can delete any transaction log backup files that were taken before the last full or differential backup.
0
 
LVL 25

Author Comment

by:Rouchie
ID: 22668346
Its a funny one.  I've never really grasped the concept of transactional backups.

My web host does a daily file backup, so I never really thought about it.  Recently though I see the log file has grown to 40MB which makes it a real pain when I download to my development machine.

I really need to know the best way to backup the database data while not ending up with huge files.   Do you know of any links where I can read in detail about backing up and what happens with the backup files etc etc?  I've read books online but it presumes you are already a pro on this.  The stuff I found online only seems to cover a small part of the puzzle...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22668362
change the recovery mode to simply, I would say, unless you plan to run regular (=hourly) transaction log backups.
be aware that you will only be able to run full restores, then, as a consequence.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 300 total points
ID: 22668365
40MB is in no way a huge file. ;)

Create a database maintenance plan.
http://blogs.techrepublic.com.com/datacenter/?p=248
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

689 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