Query Governor Cost Limit

I have enabled the query governor on our SQL2000 SP2 server with a
threshold of 3600. Now, some of the maintenance jobs fail due to the
limit being to low (e.g. one of the user databases integrity check
fails nightly).

I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
before the line in the step which reads 'EXECUTE
master.dbo.xp_sqlmaint N'-Plan etc'
but it has no effect.

Does anyone know how to get around this situation without using
sp_configure to change the query governor settings at a systemwide
level?

GC."Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0311060139.37e6f333@.posting.google.c om...
> I have enabled the query governor on our SQL2000 SP2 server with a
> threshold of 3600. Now, some of the maintenance jobs fail due to the
> limit being to low (e.g. one of the user databases integrity check
> fails nightly).
> I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
> before the line in the step which reads 'EXECUTE
> master.dbo.xp_sqlmaint N'-Plan etc'
> but it has no effect.
> Does anyone know how to get around this situation without using
> sp_configure to change the query governor settings at a systemwide
> level?
> GC.

I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
separate connection. According to BOL, the SET option only affects the
current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
is effectively an entirely separate client program.

One workaround would be to write your own maintenance procedures with all
the DBCC commands etc. in a single batch and executing in the same
connection. Or set the server default back to zero, and use the SET option
only in the code that needs it, if that's less work.

Simon|||Thanks - what I've tried to do is use the below commands but it still
ignores the QUERY GOVERNOR COST LIMIT setting so it's back to the
drawing board...

SET QUERY_GOVERNOR_COST_LIMIT 0
exec xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\sqlmaint.exe" -S SERVER3 -D data -Rpt
e:\mssql\MSSQL\LOG\data_DB_Maintenance_Plan2.txt -DelTxtRpt 4WEEKS
-WriteHistory -CkDB'

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3faa94e6$1_2@.news.bluewin.ch>...
> "Garry Clarke" <gclarke@.euro.banta.com> wrote in message
> news:fed38413.0311060139.37e6f333@.posting.google.c om...
> > I have enabled the query governor on our SQL2000 SP2 server with a
> > threshold of 3600. Now, some of the maintenance jobs fail due to the
> > limit being to low (e.g. one of the user databases integrity check
> > fails nightly).
> > I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
> > before the line in the step which reads 'EXECUTE
> > master.dbo.xp_sqlmaint N'-Plan etc'
> > but it has no effect.
> > Does anyone know how to get around this situation without using
> > sp_configure to change the query governor settings at a systemwide
> > level?
> > GC.
> I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
> sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
> separate connection. According to BOL, the SET option only affects the
> current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
> is effectively an entirely separate client program.
> One workaround would be to write your own maintenance procedures with all
> the DBCC commands etc. in a single batch and executing in the same
> connection. Or set the server default back to zero, and use the SET option
> only in the code that needs it, if that's less work.
> Simon