This will configure SQL so that it works well with RMS; from Jeff Hobbs (Check Point Software).
If HQ environment, all dbs should be configured with these settings including any offline dbs.
Run one at a time.
-- Turn on advanced options
EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
-- Set max server memory = 6400MB for an 8g Server machine -- 1/2 for 4g
EXEC sp_configure'max server memory (MB)',6400;
RECONFIGURE WITH OVERRIDE
R2: EXEC sys.sp_configure N'max server memory (MB)', N'6400'
-- Turn on Ad Hoc Queries
EXECUTE SP_CONFIGURE 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
-- Turn off advanced options
EXECUTE SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
R2: EXEC sp_configure 'show advanced option', '0'
-- Turn off AutoClose in Express versions
EXEC sp_dboption 'databasename', 'autoclose', 'FALSE'
RECONFIGURE WITH OVERRIDE
R2: script not supported or necessary? (does not always work for some reason; make sure to enter db name)
-- Change Recovery Mode to Simple to prevent log file growth
ALTER DATABASE "databasename" SET RECOVERY SIMPLE
R2: not supported or necessary?
Change the max memory line to your amount of machine's memory. Be sure to also change the 2 lines with databasename to your database.
To view all possible commands run this after enabling advanced options.
EXEC sp_configure
This also lets you know if advanced options are enabled and need to be disabled.
This should work for R2 (case sensitive?):
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
Dave J
Comments