• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1698
  • Last Modified:

The MS SQL log file growth size limit

Dear all,

 right now found out that the user database log file size can't change at all, it is stay in resticted growth even I set it to unrestricted, any reaons for this ?

DBA100.

log file size
0
marrowyung
Asked:
marrowyung
  • 11
  • 8
  • 5
2 Solutions
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If the max. size is at about 2TB, it is a display "bug" - you can't reset the "restricted growth" flag for the log file, but the maximum allowed is sufficient to be almost the same ;-).
0
 
David ToddSenior DBACommented:
Hi,

Are you focusing on the 'display bug' highlighted above, or are there other symptoms of a log file that can't grow? Are you out of disk space? Is the log trying to grow, but times-out so get out of space messages?

HTH
  David

PS 20MB for both data and log is fairly lite for a production system  ....
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"If the max. size is at about 2TB, it is a display "bug"

so what you mean is if the display limit it display is 2TB, then it is unsolveable and we can't fix it ?

yes the limit is 2TB, 2GGB.

"Are you out of disk space? Is the log trying to grow, but times-out so get out of space messages?
"
I get out of space message from time to time from the Nagios ! someone visited me for this.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
A log file of 2TB is big enough by far. Unless you have millions of transactions per second, you will never hit that limit.
You can't fix it. After restricting the log file size, you can't unset that anymore. The limit is set to 2TB instead.

"Are you out of disk space? Is the log trying to grow, but times-out so get out of space messages?"
is more than only one question. Does the disk run out of space because the log file has grown too big, and/or can't the log file be extended for that reason? A transaction log growing above reasonable size is something different then having a short-term need for more space in the transaction log (leading to resizing).
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
You mean need a change in application design? Once the log is growth more than the limit can hold,then an log with alert rise and monitoring tools detect it.  

this is not what we want to see.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
All I want to say is that you need to analyze why the log file might want to increase. I'm not talking about a size of 1 GB (which might be appropriate) or below. If the log file grows all the time, you have
a) the wrong application design
b) the wrong recovery mode of the SQL DB (i.e. you need Simple only, but have Full)
c) the wrong backup strategy - if your recovery mode is not simple, the transaction log must be backed up separately!

You are not telling us any details, and are not precise in your intentions or needs, so all we can give is generic advice, and guess what you are after. You were asked directly what your core problem is, and haven't answered to that at all yet.
0
 
David ToddSenior DBACommented:
Hi,

Do you have transaction log backups?

What recovery model are you using?

Regards
  David
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Qlemo,

"the wrong recovery mode of the SQL DB (i.e. you need Simple only, but have Full)"

we are already in simple mode.

"You are not telling us any details, and are not precise in your intentions or needs, so all we can give is generic advice, and guess what you are after. "

can't see why you say this, what else I didn't answer?

dtodd,

"Do you have transaction log backups?"

no I don't have as we are using simple mode only !
0
 
David ToddSenior DBACommented:
Hi,

From http://msdn.microsoft.com/en-us/library/ms143432.aspx

Maximum Capacity Specifications for SQL Server
File size (data)    16 terabytes
File size (log)         2 terabytes

So when you see limited to 2,097,152MB that is pretty much 2TB, which is as big as it can get. If this isn't enough, then you can add another log file.

Given how little we've been told about your system, I had to ask about the recovery model and transaction log backups!

Regards
  David
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
dtodd,

"we are already in simple mode."

"no I don't have as we are using simple mode only ! "


"So when you see limited to 2,097,152MB that is pretty much 2TB, which is as big as it can get. If this isn't enough, then you can add another log file."

I think I can shink the log file and see what is going on.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
right now I willl only shrink the log in concern. other wise I might add one more log file.

so if the SQL server find the primary log file is fulled and it will START to write to the new log file ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I'll try to ask precise questions:
1. Are you worried about the log file not being able to grow beyond 2 TB?
2. What is you current log file size?
3. How much of the log file is in use?
4. How much space remains on the disk?

Shrinking log files should be done sparsely. Usually you do that after log-file intense, but "unusual" operations like cleanup/archiving, batch processing of a lot of data etc. done only once in a while. It is a good idea to shrink the log file, then monitor its growth daily over let's say a week, to get a profile. In everyday usage the log file should stop at a certain size, using up and releasing some "logical" space. However, if it grows all the time, something went wrong - usually uncommitted transactions remain, so the log file cannot get truncated.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
1. Are you worried about the log file not being able to grow beyond 2 TB?

no.  it try to growth beyond that. and error rise in the monitoring tools say it is full.

2. What is you current log file size?

2TB.

3. How much of the log file is in use?

26MB.

4. How much space remains on the disk?

112GB.

1
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Then something is wrong. Something eats up the 2TB log file. You'll have to monitor the amount of used log file over a longer period. I cannot imagine any process acting on a small database to consume 2TB log file ...
0
 
David ToddSenior DBACommented:
Hi,

Echoing Qlemo above ...

What size is your datafile(s)?

If the log is 2TB then I assume that you have terabytes to petabytes of data ...

Else something is really wrong with the application.

At those sizes I suggest setting up a management data warehouse and using this to monitor data file useage ... Yes it uses something like 10-20GB per monitored instance, but it seems you can easily afford that.

Regards
  David

PS a MDW needs SQL 2008 - what version/edition of SQL are you using?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"What size is your datafile(s)?"

for that DB has problem,the MDF file has only 6.5GB.

"Else something is really wrong with the application."

probably.

"At those sizes I suggest setting up a management data warehouse and using this to monitor data file useage ... Yes it uses something like 10-20GB per monitored instance, but it seems you can easily afford that."

why I need one ? what is the MDW you are talking about ? please specify, I am using SQL server 2008 R2 standard edition.

monitor data file usage can be the works of a disk usage monitor tools, right? no need warehouse ?
0
 
David ToddSenior DBACommented:
Hi

Google how to setup & use a MDW, as it is FREE - okay, part of SQL 2008R2. So don't buy a tool; use the tool provided with SQL.

Regards
  David
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
why we need this but not a disk usage monitoring tools? we can have one inhouse for this, Nagios can be free.
0
 
David ToddSenior DBACommented:
Hi

Because the MDW and monitoring shows usage of the data and transaction files.

This will indicate if and when large transactions hit.

Regards
  David
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
so this is very different from what disk monitoring tools tells ?

MDW can run on SQL server 2008 R2 standard edition and need reporting service?

I think I ask some other quetsions and someone propose this before...
0
 
David ToddSenior DBACommented:
Hi

I ran a MDW on standard Ed.

Doesn't need reporting services.

Regards
  David
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
good, hope the link you show me guide me how to make use of it til the situation i can find out the problem.

The error comes up again and I just add one more log file.
0
 
David ToddSenior DBACommented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
you show this to me already.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 11
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now