Solved

The MS SQL log file growth size limit

Posted on 2013-06-15
24
1,060 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
[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
  • 11
  • 8
  • 5
24 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 39249862
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
ID: 39250609
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
ID: 39250866
"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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 39250999
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
ID: 39251163
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 70

Expert Comment

by:Qlemo
ID: 39251166
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
ID: 39251986
Hi,

Do you have transaction log backups?

What recovery model are you using?

Regards
  David
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39252134
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
ID: 39252159
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
ID: 39252202
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
ID: 39252360
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 70

Expert Comment

by:Qlemo
ID: 39252424
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39252709
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 70

Expert Comment

by:Qlemo
ID: 39253004
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
ID: 39254201
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
ID: 39292421
"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
ID: 39292441
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
ID: 39295452
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
ID: 39295863
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
ID: 39295871
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
ID: 39295929
Hi

I ran a MDW on standard Ed.

Doesn't need reporting services.

Regards
  David
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39295942
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
ID: 39296087
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39296126
you show this to me already.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

632 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