SQL Server 安装后部分选项初始化脚本

sql server安装后，根据对应的业务场景，数据库实例的部分选项需要调整，例如实例的最大内存、tempdb 文件的增长量、job执行记录数等等，但这一步经常被大家忽略掉。

其实很多选项初始化都可以通过脚本实现，一件执行。下面是一些常见选项初始的例子，仅供大家参考。

use master
go
--show advanced options
sp_configure 'show advanced options',1
reconfigure with override
go
print 'show advanced options ok'
go


--enable extend stored procedure xp_cmdshell
sp_configure 'xp_cmdshell',1
reconfigure with override
go
print 'enabled xp_cmdshell ok'
go

---------------------------------------------------------
---------configure how many memory server will used------
---------------------------------------------------------
declare @i as bigint
select @i=ceiling(total_physical_memory_kb/1024/1024.0) from sys.dm_os_sys_memory
    if @i*0.2>20
        begin
            --set @i=(@i-10)*1024
            set @i=20*1024
        end
    else if @i*0.2<4
        begin
            set @i=4*1024
        end    
    else
        begin
            set @i=@i*0.8*1024
        end    
--configure how many memory server will used
exec sp_configure 'max server memory',@i    --（该参数根据sever内存大小具体决定）
reconfigure with override
go
print 'set max memory ok'
go
---------------------------------------------------------
---------------------------------------------------------
---------------------------------------------------------

 --set database compress backup 
exec sp_configure 'backup compression default', '1';
reconfigure with override

-----------配置代理错误日志文件-------------------------------------------------------
exec msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=50000,@jobhistory_max_rows_per_job=100
go
print n'配置代理错误日志文件 成功'
go


----------configure temp db's file growth ------------
alter database [tempdb] modify file ( name = n'tempdev', filegrowth = 100mb )
alter database [tempdb] modify file ( name = n'templog', filegrowth = 100mb )

----enable create ole auotmation (sp_oacreate)
--sp_configure 'ole automation procedures',1
--reconfigure with override
--go
--print 'enabled sp_oacreate ok'
--go

----enable distributed query/transaction in ad hoc query like opendatasource/operquery
--sp_configure 'ad hoc distributed queries',1
--reconfigure with override
--go
--print 'enabled  opendatasource/operquery ok'
--go