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