Log full alert

Hi,
I want to create a job that will raise an error case Log is getting almost
full.
Is there a sp (or any other tool) that can assist?
Thanks, Hagay.Hi all,
I've found a way to extract log precentage usage:
master db has a table msysperinfo which holds (among other detail) log
precentage use of each database.
so the following query will give you what you need:
select cntr_value
from msysperfinfo
where counter_name = 'Percent Log Used'
and instance_name = @.databaseName
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to create a job that will raise an error case Log is getting almost
> full.
> Is there a sp (or any other tool) that can assist?
> Thanks, Hagay.
>
>|||Maybe you have ran a script to create that table as I don't think it is a
standard table, you could do
dbcc sqlperf('logspace')
to get log information and space used/free, from there you could throw that
to a table and check for percentage used to send an alert.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Hagay Lupesko" <hagayl@.nice.com> wrote in message
news:ezOTZOOkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I've found a way to extract log precentage usage:
> master db has a table msysperinfo which holds (among other detail) log
> precentage use of each database.
> so the following query will give you what you need:
> select cntr_value
> from msysperfinfo
> where counter_name = 'Percent Log Used'
> and instance_name = @.databaseName
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I want to create a job that will raise an error case Log is getting
almost
> > full.
> > Is there a sp (or any other tool) that can assist?
> >
> > Thanks, Hagay.
> >
> >
> >
>|||The counter in the sysperfinfo table and the value from DBCC
SQLPERF(LOGSPACE) are essentially the same. You can use these values to
alert you on a certain log usage threhold (e.g. 85%), if your log files are
not set to autogrow.
However, if one of your log files is set to autogrow, these values will
often give false alarms. For instance, your log might be 90% used, but if
you still have 10GB of free space of the drive on which the log file is set
to autogrow, you may not be too pleased to be paged at 3 AM.
In short, your script needs to consider the autogrow property, and it can
become rather complex if you have multiple log files on different drives.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:uQljKlOkDHA.2416@.TK2MSFTNGP10.phx.gbl...
> Maybe you have ran a script to create that table as I don't think it is a
> standard table, you could do
> dbcc sqlperf('logspace')
> to get log information and space used/free, from there you could throw
that
> to a table and check for percentage used to send an alert.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> news:ezOTZOOkDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Hi all,
> >
> > I've found a way to extract log precentage usage:
> >
> > master db has a table msysperinfo which holds (among other detail) log
> > precentage use of each database.
> >
> > so the following query will give you what you need:
> >
> > select cntr_value
> > from msysperfinfo
> > where counter_name = 'Percent Log Used'
> > and instance_name = @.databaseName
> >
> > "Hagay Lupesko" <hagayl@.nice.com> wrote in message
> > news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I want to create a job that will raise an error case Log is getting
> almost
> > > full.
> > > Is there a sp (or any other tool) that can assist?
> > >
> > > Thanks, Hagay.
> > >
> > >
> > >
> >
> >
>|||Just let Agent do this using a performance condition alert!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hagay Lupesko" <hagayl@.nice.com> wrote in message news:emMTx0MkDHA.392@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to create a job that will raise an error case Log is getting almost
> full.
> Is there a sp (or any other tool) that can assist?
> Thanks, Hagay.
>
>

Log File size increase

I have set up an alert for Percent log used and i get this message every now and then:

The SQL Server performance counter 'Percent Log Used' (instance 'TelehopBilling') of object 'SQLServer:Databases' is now above the threshold of 90.00 (the current value is 95.00).

My database settings are:

Transaction log files space allocated

file1 1 mb

file 2 3 mb

file 3 1201 mb

automatically grow file : not checked

auto shrink - off

shrinking of database thru auto task: twice a week.

Please guide what is the solution to this. Also suggest me a suitable database settings. This is a production database and the actual log file size is around 2 GB

Thanks,

Why you are using multiple files for Transaction log?

As you might not gain anything using this way, I would suggest to use only one file and set appropriate size to transaction log. At the same time do not waste the SQL resources by shrinking the log regularly. You need to consider the set of processess including day to day, scheduled jobs such as database maintenance tasks, any bulk insert jobs and during these processes it will have impact on size.

Also ensure to maintain the backup log schedule frequently to take care of virtual log size boundaries that will stop unprecedented growth during any process.

As of now your setup seems ok other than having multiple log files, also I don't understand what is the problem you have with this setup.

Log deletion of db user and send email alert

Can't work out how to do the following:
We have a database in which a user keeps being deleted at
random intervals.
How do i configure SQL to email me when this user is
deleted?
I tried to create a delete trigger on the sysusers table
which raises a user defined error which is written to the
windows event log. SQL Agent then reads the event log and
fires off an email to me. HoweverI don't think you can
create triggers on system tables (I get a permission
denied message when I try to create this trigger, even
though I'm a system admin).
There must be an easy solution to this?You could try to setup a job that looks to see if that user exists in
syslogins/sysusers, then fire off an email if it doesn't. Set the job to
run ever 15 minutes or so.
Rather than approaching the problem this way, I would run SQL Profiler and
try to find out exactly what's happening when this user is deleted and why
it's happening.
"Stu" <stu_hearn@.yahoo.co.uk> wrote in message
news:0bd001c352c7$80d4dff0$a501280a@.phx.gbl...
> Can't work out how to do the following:
> We have a database in which a user keeps being deleted at
> random intervals.
> How do i configure SQL to email me when this user is
> deleted?
> I tried to create a delete trigger on the sysusers table
> which raises a user defined error which is written to the
> windows event log. SQL Agent then reads the event log and
> fires off an email to me. HoweverI don't think you can
> create triggers on system tables (I get a permission
> denied message when I try to create this trigger, even
> though I'm a system admin).
> There must be an easy solution to this?
>
>|||I did think about using profiler, however its usually a
period of weeks between deletions of this user, so this
would mean leaving profiler running continuosly and i
would have to monitor profiler each day to look for this
event. I'd prefer to be notified as the deletion occurs.
>--Original Message--
>You could try to setup a job that looks to see if that
user exists in
>syslogins/sysusers, then fire off an email if it
doesn't. Set the job to
>run ever 15 minutes or so.
>Rather than approaching the problem this way, I would run
SQL Profiler and
>try to find out exactly what's happening when this user
is deleted and why
>it's happening.
>
>"Stu" <stu_hearn@.yahoo.co.uk> wrote in message
>news:0bd001c352c7$80d4dff0$a501280a@.phx.gbl...
>> Can't work out how to do the following:
>> We have a database in which a user keeps being deleted
at
>> random intervals.
>> How do i configure SQL to email me when this user is
>> deleted?
>> I tried to create a delete trigger on the sysusers table
>> which raises a user defined error which is written to
the
>> windows event log. SQL Agent then reads the event log
and
>> fires off an email to me. HoweverI don't think you can
>> create triggers on system tables (I get a permission
>> denied message when I try to create this trigger, even
>> though I'm a system admin).
>> There must be an easy solution to this?
>>
>
>.
>|||Yup, that certainly presents a problem to using profiler. You may be able to
still use it looking for only specific events--that wouldn't put too much
strain on the server, however you probably wouldn't get enough info to do
any real troubleshooting.
You might want to add another step to that periodic job to take a snapshot
of sysprocesses -- at least you might be able to see who's logged in when
this seems to occur. There may be some consistency there.
Anyways, tough situation. Good luck!
"Stu" <stu_hearn@.yahoo.co.uk> wrote in message
news:1cbb01c352ce$de3aec50$a001280a@.phx.gbl...
> I did think about using profiler, however its usually a
> period of weeks between deletions of this user, so this
> would mean leaving profiler running continuosly and i
> would have to monitor profiler each day to look for this
> event. I'd prefer to be notified as the deletion occurs.
>
> >--Original Message--
> >You could try to setup a job that looks to see if that
> user exists in
> >syslogins/sysusers, then fire off an email if it
> doesn't. Set the job to
> >run ever 15 minutes or so.
> >
> >Rather than approaching the problem this way, I would run
> SQL Profiler and
> >try to find out exactly what's happening when this user
> is deleted and why
> >it's happening.
> >
> >
> >"Stu" <stu_hearn@.yahoo.co.uk> wrote in message
> >news:0bd001c352c7$80d4dff0$a501280a@.phx.gbl...
> >> Can't work out how to do the following:
> >>
> >> We have a database in which a user keeps being deleted
> at
> >> random intervals.
> >>
> >> How do i configure SQL to email me when this user is
> >> deleted?
> >>
> >> I tried to create a delete trigger on the sysusers table
> >> which raises a user defined error which is written to
> the
> >> windows event log. SQL Agent then reads the event log
> and
> >> fires off an email to me. HoweverI don't think you can
> >> create triggers on system tables (I get a permission
> >> denied message when I try to create this trigger, even
> >> though I'm a system admin).
> >> There must be an easy solution to this?
> >>
> >>
> >>
> >
> >
> >.
> >