Solved

The MS SQL log file growth size limit

Posted on 2013-06-15
24
897 Views
Last Modified: 2013-07-03
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
Comment
Question by:marrowyung
  • 11
  • 8
  • 5
24 Comments
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

Do you have transaction log backups?

What recovery model are you using?

Regards
  David
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
why we need this but not a disk usage monitoring tools? we can have one inhouse for this, Nagios can be free.
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi

I ran a MDW on standard Ed.

Doesn't need reporting services.

Regards
  David
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
you show this to me already.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now