High performance isn't always an advantage...

Hello
MSSQL 2000 + SP3 on Windows 2000 AS + SP4
I've moved my databases to new server.
IO subsystem now TEN times faster!
I have the system of two jobs performing defragmentation
of my data. One of the jobs issues DBCC INDEXDEFRAG
statements and the second one monitors state of transaction
log and stops first job if log more than 70% full. SQLAgent
can run jobs only with 1 minute intervals. So I can monitor
log status only once in minute. Everything was all right at old
server. Now on new hardware log overfills in couple of seconds
and monitoring job can't catch the moment of overfilling and
correctly stop the defragmentation.
Two questions:
1. Should I increase Tlog size depending on hardware performance
2. Is there any way to monitor log state more ofter than once
in a minute?
Thanks.
Serge ShakhovYou can have a job which uses an endless loop and WAITFOR with, say, 10 seconds.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Serge Shakhov" <REMOVETHIS_ACETYLENE@.mail.ru> wrote in message
news:83ndmb.5kr.ln@.proxyserver.ctd.mmk.chel.su...
> Hello
> MSSQL 2000 + SP3 on Windows 2000 AS + SP4
> I've moved my databases to new server.
> IO subsystem now TEN times faster!
> I have the system of two jobs performing defragmentation
> of my data. One of the jobs issues DBCC INDEXDEFRAG
> statements and the second one monitors state of transaction
> log and stops first job if log more than 70% full. SQLAgent
> can run jobs only with 1 minute intervals. So I can monitor
> log status only once in minute. Everything was all right at old
> server. Now on new hardware log overfills in couple of seconds
> and monitoring job can't catch the moment of overfilling and
> correctly stop the defragmentation.
> Two questions:
> 1. Should I increase Tlog size depending on hardware performance
> 2. Is there any way to monitor log state more ofter than once
> in a minute?
> Thanks.
> Serge Shakhov
>|||Hello
> You can have a job which uses an endless loop and WAITFOR with, say, 10
seconds.
That's a good idea!
But I'll try to avoid cycling process by executing every minute job wich
will last 58-59 seconds and perform checks say every 5 seconds...
Thank you Tibor.
Serge Shakhovsql