Post reply

Percent Log Used alert

  • October 12, 2005 at 11:47 am

    #91418

    Hi,

    I am trying to get my Oracle mind around this problem that I am having with a job.  I understand the concepts of the transaction log and I have setup a job that backs up the transaction log when the Percent Log Used threshold rises above 85%.  This works great when they are loading data into the data warehouse over the weekend and need more log space.  The problem happened today when a table was being loaded and I got alert emails saying:

    DESCRIPTION: The SQL Server performance counter 'Percent Log Used' (instance 'jbdw_warehouse') of object 'SQLServeratabases' is now above the threshold of 85.00 (the current value is 99.00).

    This kicked off the job and it backed up the log.  But when I went into SQL Enterprise Manager and showed the properties of the database, the log size was 3.4GB and it had a restricted file growth size of 6GB.  This was only about 60% used so why did it kick off the backup job?  It then preceed to alert me every minute for 15 minutes that it was kicking off the job becuase the log was above the threshold.  I am just trying to understand what values is SQL looking at when determining this.  Is there a better way to check the log size when I get this alert that is more accurate?  Because the way it looks now, the log still has plenty of room before it reaches 85% full and yet the job still kicks off and I go to the path and see the backup of the log file.

    Any clarification on this would be greatly appreciated!

    Thanks,

    Bea

    Thanks!
    Bea Isabelle

  • October 12, 2005 at 11:52 am

    #596896

    Isabelle,

    the 85 % threshold is based on the current logfile size not on the max allowed size of 6Gb.

    If you only want the logfile backuped then 85 % of 6 gb are used, define the size of the T-log as 6 gb and disable autogrow.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • October 12, 2005 at 12:14 pm

    #596903

    Hi Marcus,

    I see that the properties on the log file have Automatically grow file checked and Restrict file growth to 6GB.  I thought this was pretty much saying to set a maximum size you want the log file to be and autogrow until it reaches that size.  If I disable the Automatically grow file and set Restrict file growth to 6gb, will it just grow when it needs to?  And if so, what is the Automatically Grow file property used for?

    Thanks!

    Bea

    Thanks!
    Bea Isabelle

  • October 12, 2005 at 12:53 pm

    #596919

    I see that the properties on the log file have Automatically grow file checked and Restrict file growth to 6GB.  I thought this was pretty much saying to set a maximum size you want the log file to be and autogrow until it reaches that size. 

    That's exactly the case

    If I disable the Automatically grow file and set Restrict file growth to 6gb, will it just grow when it needs to? 

    No, you would have to set the size to 6Gb or in other words use manual growth.
     
    From BOL:

    SQL Server 2000 files can grow automatically from their originally specified size. When you define a file, you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. If there are multiple files in a filegroup, they do not autogrow until all the files are full. Growth then occurs using a round-robin algorithm.

    Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the user does not have ready access to a system administrator. The user can let the files autogrow as needed to lessen the administrative burden of monitoring the amount of free space in the database and allocating additional space manually.

    SQL Server 2000 files can grow automatically from their originally specified size. When you define a file, you can specify a growth increment. Each time the file fills, it increases its size by the growth increment. If there are multiple files in a filegroup, they do not autogrow until all the files are full. Growth then occurs using a round-robin algorithm.

    Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. This feature is especially useful when SQL Server is used as a database embedded in an application where the user does not have ready access to a system administrator. The user can let the files autogrow as needed to lessen the administrative burden of monitoring the amount of free space in the database and allocating additional space manually.

    Markus

    [font="Verdana"]Markus Bohse[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply