By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,218 Members | 1,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,218 IT Pros & Developers. It's quick & easy.

Query Governor Cost Limit

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Garry Clarke" <gc*****@euro.banta.com> wrote in message
news:fe**************************@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
Jul 20 '05 #2

P: n/a
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" <sq*@hayes.ch> wrote in message news:<3f**********@news.bluewin.ch>...
"Garry Clarke" <gc*****@euro.banta.com> wrote in message
news:fe**************************@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

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.