Log File Size Maintenance

Hi,
I am using SQL2000 Standard.
The log file running up tremendously after the system life in production.
My ratio of size is 200mb for Data files and 650mb for Log file, in a month.
Apparently I wish to know:
1) What is cause of the Log file turn big? I am worry it was cause by error
(any error eg, ADO or SQL Transaction Log).
2) It is possible to read / study the log file content?
3) How to Backup or Truncate the log file?
4) What is the Standard or Typical settting in SQL Server Properties and
Configuration in regards to Log File maintenance? I am using all default
setting now.
Thanks.> 1) What is cause of the Log file turn big? I am worry it was cause by
error
> (any error eg, ADO or SQL Transaction Log).
Full or Bulk Logged recovery model without a backup plan, I guess in your
case.
> 2) It is possible to read / study the log file content?
Log Explorer - www.lumigent.com.
> 3) How to Backup or Truncate the log file?
With Backup Log T-SQL statement. Check the syntax in Books OnLine.
> 4) What is the Standard or Typical settting in SQL Server Properties and
> Configuration in regards to Log File maintenance? I am using all default
> setting now.
Default settings differ for server and desktop editions for SQL Server, so
you didn't give us much info. Anyway, for a server in production, you should
use Full recovery model with appropriate backup plan.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Log Cache Hit Ratio + Buffer Cache Hit Ratio

Thanks in advance for any info.
SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
RAID 5.
What does one do when the Buffer Cache is at (and stays at) 99% and the Log
Cache is at 99%? Faster processors, more memory, change the RAID config?
Note: this is not daily activity on the box, but a monthly process that has
gone from 18 hours to 28 hours {and counting} with no changes.
Thanks,
MorganIf you're talking about the hit ratios, those are both
excellent numbers. Anything over 95% is optimal. When
those numbers start to drop, in general more memory is
the key, but be sure to check into it more closely before
purchasing hardware.
>--Original Message--
>Thanks in advance for any info.
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I
think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays
at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change
the RAID config?
>Note: this is not daily activity on the box, but a
monthly process that has
>gone from 18 hours to 28 hours {and counting} with no
changes.
>
>Thanks,
>Morgan
>
>.
>|||Morgan,
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
99% cache hit ratios are optimal.
This means that the server is practically always getting data from the
caches, rather than going to the disk subsystem.
It sounds more like the work needs to be done in the monthly process, as far
as optimizing it goes. Your hardware seems to be performing optimally.
When did it go from 18 to 28 hours? Suddenly? Over time? I would start
suspecting one of the following:
That perhaps either:
A.) The data set has grown
B.) Someone changed one of the queries
C.) Someone dropped a key index
D.) One or more of the above. :-)
James Hokes|||In addition to the other comments the log can be a bottle neck if on a Raid
5 and especially with the data. The Log cache ration doesn't mean much for
writes so you may want to check your disk queues on the Raid that houses the
log file anyway and make sure it has no issues. TempDB can be another place
to look. Usually month end type process does a lot of activity that uses
temp tables and you can have bottlenecks there as well. Essentially you
need to monitor the cpu and disk counters while this process is happening to
see where the bottleneck is coming from. If the data cache ratio is 99%
chances are more memory won't help much. If your procedures are optimized
then it will boil down to disk or cpu. But how sure are you that they are
that optimized?
--
Andrew J. Kelly
SQL Server MVP
"Morgan" <mfears@.spamcop.net> wrote in message
news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> Thanks in advance for any info.
> SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
RAM.
> RAID 5.
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
>
> Thanks,
> Morgan
>|||On Fri, 26 Dec 2003 13:42:08 -0500, "Morgan" wrote:
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change the RAID config?
>Note: this is not daily activity on the box, but a monthly process that has
>gone from 18 hours to 28 hours {and counting} with no changes.
In addition to what James, James and Andrew said, look at your
statistics. As your database grows over time, the distribution of values
across columns can change substantially. Out of date stats can
contribute to bad query plans, leading to long runs.
Are you able to pinpoint which part(s) of your monthly process chews the
most time?
cheers,
Ross
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello|||Thanks to everyone for their insight.
I was somewhat under the impression that if they're both max'd out, then
it's time to start looking at HW. I know for a fact the procedures need
"help", but it's an inherited process that for the first time this month,
took much longer than usual, which is why I knee-jerked my post. To the best
of my knowledge, none of the underlying objects has changed, but upon
further review (below), several tables are missing indicies where they are
required. I also saw quite a bit of parallelism happening via SP_WHO, and
have begun to question the multi-proc machine, which for better or worse, is
most likely fine.
I ran Profiler off-and-on (didn't want to make it any worse than it was),
and found a single procedure that appeared to be somewhat of a bottleneck,
lots of extensive reads for a single query, minimal (single, actually)
writes. Fortunately, a predecessor had the forethought to not use temp
tables, however, the tables in use are not properly indexed. Upon review of
the primary table in the afore mentioned procedure, it turns out there are
no indexes or unique constraints; along with a truncate table statement
against the table at the start of the procedure. I know the cost of a
properly placed index will fix the amount of reads against the suspect table
I saw in Profiler. Top all this off with cursors galore (note: not the
author, but the maintainer), and you can easily appreciate the mess I'm
dealing with. I'm reading this as the beginning of the end for the current
processes... ;)
Thanks again,
Morgan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOYObWMzDHA.2408@.tk2msftngp13.phx.gbl...
> In addition to the other comments the log can be a bottle neck if on a
Raid
> 5 and especially with the data. The Log cache ration doesn't mean much for
> writes so you may want to check your disk queues on the Raid that houses
the
> log file anyway and make sure it has no issues. TempDB can be another
place
> to look. Usually month end type process does a lot of activity that uses
> temp tables and you can have bottlenecks there as well. Essentially you
> need to monitor the cpu and disk counters while this process is happening
to
> see where the bottleneck is coming from. If the data cache ratio is 99%
> chances are more memory won't help much. If your procedures are optimized
> then it will boil down to disk or cpu. But how sure are you that they are
> that optimized?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Morgan" <mfears@.spamcop.net> wrote in message
> news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> > Thanks in advance for any info.
> >
> > SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
> RAM.
> > RAID 5.
> >
> > What does one do when the Buffer Cache is at (and stays at) 99% and the
> Log
> > Cache is at 99%? Faster processors, more memory, change the RAID config?
> > Note: this is not daily activity on the box, but a monthly process that
> has
> > gone from 18 hours to 28 hours {and counting} with no changes.
> >
> >
> >
> > Thanks,
> > Morgan
> >
> >
>

Log Cache Hit Ratio

Hi,
What would be the optimum percentage for the Log Cache Hit
Ratio?
Does this indicator at all have any purpose or
implications?
Thanks,
OskarI have never found a situation where that counter provided anything
useful... It has the same meaning as the data cache hit ratio, but applies
to log reads from the log cache.. ( I am not aware of any method to
configure the log cache either...)
Is your log cache hit ratio < 90%'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
> Hi,
> What would be the optimum percentage for the Log Cache Hit
> Ratio?
> Does this indicator at all have any purpose or
> implications?
> --
> Thanks,
> Oskar|||Hi Wayne,
Yup, constantly at 50 %.
Wondering what this could mean and is it OK or not.
By the way I have SQL Server: Cache Manager: Cache Hit
Ratio steady at 70%.
Thanks,
Oskar

>--Original Message--
>I have never found a situation where that counter
provided anything
>useful... It has the same meaning as the data cache hit
ratio, but applies
>to log reads from the log cache.. ( I am not aware of any
method to
>configure the log cache either...)
>Is your log cache hit ratio < 90%'
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
Hit[vbcol=seagreen]
>
>.
>|||Wayne,
I've never bother with this counter much at all? What is it really
measuring? Rollbacks? Reading from inserted/deleted tables in triggers?
Brian
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:evKwbO2tEHA.3476@.TK2MSFTNGP14.phx.gbl...
> I have never found a situation where that counter provided anything
> useful... It has the same meaning as the data cache hit ratio, but applies
> to log reads from the log cache.. ( I am not aware of any method to
> configure the log cache either...)
> Is your log cache hit ratio < 90%'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Oskar" <anonymous@.discussions.microsoft.com> wrote in message
> news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
>

Log Cache Hit Ratio

Hi,
What would be the optimum percentage for the Log Cache Hit
Ratio?
Does this indicator at all have any purpose or
implications?
Thanks,
Oskar
I have never found a situation where that counter provided anything
useful... It has the same meaning as the data cache hit ratio, but applies
to log reads from the log cache.. ( I am not aware of any method to
configure the log cache either...)
Is your log cache hit ratio < 90%?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
> Hi,
> What would be the optimum percentage for the Log Cache Hit
> Ratio?
> Does this indicator at all have any purpose or
> implications?
> --
> Thanks,
> Oskar
|||Hi Wayne,
Yup, constantly at 50 %.
Wondering what this could mean and is it OK or not.
By the way I have SQL Server: Cache Manager: Cache Hit
Ratio steady at 70%.
Thanks,
Oskar

>--Original Message--
>I have never found a situation where that counter
provided anything
>useful... It has the same meaning as the data cache hit
ratio, but applies
>to log reads from the log cache.. ( I am not aware of any
method to
>configure the log cache either...)
>Is your log cache hit ratio < 90%?
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
Hit
>
>.
>
|||Wayne,
I've never bother with this counter much at all? What is it really
measuring? Rollbacks? Reading from inserted/deleted tables in triggers?
Brian
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:evKwbO2tEHA.3476@.TK2MSFTNGP14.phx.gbl...
> I have never found a situation where that counter provided anything
> useful... It has the same meaning as the data cache hit ratio, but applies
> to log reads from the log cache.. ( I am not aware of any method to
> configure the log cache either...)
> Is your log cache hit ratio < 90%?
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Oskar" <anonymous@.discussions.microsoft.com> wrote in message
> news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
>

Log Cache Hit Ratio

Hi,
What would be the optimum percentage for the Log Cache Hit
Ratio?
Does this indicator at all have any purpose or
implications?
--
Thanks,
OskarI have never found a situation where that counter provided anything
useful... It has the same meaning as the data cache hit ratio, but applies
to log reads from the log cache.. ( I am not aware of any method to
configure the log cache either...)
Is your log cache hit ratio < 90%'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
> Hi,
> What would be the optimum percentage for the Log Cache Hit
> Ratio?
> Does this indicator at all have any purpose or
> implications?
> --
> Thanks,
> Oskar|||Hi Wayne,
Yup, constantly at 50 %.
Wondering what this could mean and is it OK or not.
By the way I have SQL Server: Cache Manager: Cache Hit
Ratio steady at 70%.
--
Thanks,
Oskar
>--Original Message--
>I have never found a situation where that counter
provided anything
>useful... It has the same meaning as the data cache hit
ratio, but applies
>to log reads from the log cache.. ( I am not aware of any
method to
>configure the log cache either...)
>Is your log cache hit ratio < 90%'
>
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Mariner, Charlotte, NC
>www.mariner-usa.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
>> Hi,
>> What would be the optimum percentage for the Log Cache
Hit
>> Ratio?
>> Does this indicator at all have any purpose or
>> implications?
>> --
>> Thanks,
>> Oskar
>
>.
>|||Wayne,
I've never bother with this counter much at all? What is it really
measuring? Rollbacks? Reading from inserted/deleted tables in triggers?
--
Brian
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:evKwbO2tEHA.3476@.TK2MSFTNGP14.phx.gbl...
> I have never found a situation where that counter provided anything
> useful... It has the same meaning as the data cache hit ratio, but applies
> to log reads from the log cache.. ( I am not aware of any method to
> configure the log cache either...)
> Is your log cache hit ratio < 90%'
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Oskar" <anonymous@.discussions.microsoft.com> wrote in message
> news:1d8c01c4b752$e8163fb0$a501280a@.phx.gbl...
> > Hi,
> >
> > What would be the optimum percentage for the Log Cache Hit
> > Ratio?
> >
> > Does this indicator at all have any purpose or
> > implications?
> >
> > --
> > Thanks,
> > Oskar
>