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