Solved

The MS SQL log file growth size limit

Posted on 2013-06-15
24
932 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 69

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

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 69

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 69

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 69

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 47
Simple SQL query from two tables 13 51
Sql server function help 15 27
Need some help to cast ntext to nvarchar SQL 2000 7 30
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

816 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

10 Experts available now in Live!

Get 1:1 Help Now