SERVER CONFIGURATION SCRIPTS.

setting up TEMPDB,Memory and CPU configuration settings once after the SQL server installation. 

step :1  backup settings. 

-- backup compression

EXEC sys.sp_configure N'backup compression default', N'1'

GO

RECONFIGURE WITH OVERRIDE

GO

-- to create variable length backup files.

DBCC TRACEON (3042, -1)

Go

 Step 2 :- setting-up memory settings. 

-- for SQL SERVER 2008R2

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE

go

declare @memorysize int

declare @minmemorysize int

declare  @configure  varchar(2000)

declare @setmemorysize int

select @memorysize = ((physical_memory_in_bytes/1024)/1024)/1024  from sys.dm_os_sys_info

set @setmemorysize =   (@memorysize)*0.9

set @setmemorysize=  @setmemorysize *1024

print @setmemorysize

set @minmemorysize =2000

set @configure   = 'EXEC sys.sp_configure N' +'''min server memory (MB)'''+','+' N'''+ CONVERT( varchar(50), @minmemorysize) +''''

+'  RECONFIGURE WITH OVERRIDE  '

print @configure

execute (@configure)

set @configure   = 'EXEC sys.sp_configure N' +'''max server memory (MB)'''+','+' N'''+ CONVERT( varchar(50), @setmemorysize) +''''

+'  RECONFIGURE WITH OVERRIDE  '

print @configure

execute (@configure)

go

-- for SQL 2012 and above

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE

go

declare @memorysize int

declare  @configure  varchar(2000)

declare @setmemorysize int

declare @setmemorysize_default int

select @memorysize = (((physical_memory_kb)/1024)/1024) from sys.dm_os_sys_info

set @setmemorysize =   (@memorysize)*0.9

set @setmemorysize=  @setmemorysize *1024

set @setmemorysize_default =0

print @setmemorysize

set @minmemorysize =2000

set @configure   = 'EXEC sys.sp_configure N' +'''min server memory (MB)'''+','+' N'''+ CONVERT( varchar(50), @setmemorysize_default) +''''

+'  RECONFIGURE WITH OVERRIDE  '

print @configure

execute (@configure)

set @configure   = 'EXEC sys.sp_configure N' +'''min server memory (MB)'''+','+' N'''+ CONVERT( varchar(50), @setmemorysize) +''''

+'  RECONFIGURE WITH OVERRIDE  '

print @configure

execute (@configure)

set @configure   = 'EXEC sys.sp_configure N' +'''max server memory (MB)'''+','+' N'''+ CONVERT( varchar(50), @setmemorysize) +''''

+'  RECONFIGURE WITH OVERRIDE  '

print @configure

execute (@configure)

 Step 3 :- setting-up Tempdb Settings. 

declare @cpu_cnt int

declare @filecnt int

declare @addfiles varchar(max)

SELECT  @cpu_cnt = cpu_count FROM    sys.dm_os_sys_info ;

set @filecnt = 0;

while @cpu_cnt >0

begin

set @filecnt =@filecnt+1

set @addfiles = 'ALTER DATABASE tempdb

ADD FILE(NAME =' +''''+'TEMPDEV_'+rtrim(ltrim(cast( @filecnt as char(3))))+''''+ ','+

 'FILENAME =' +''''+'P:\TEMPDEV_'+rtrim(ltrim(cast(@filecnt as char(2))))+'.NDF'+ ''''+

',SIZE = 1024MB,

MAXSIZE=1024MB,

FILEGROWTH = 10%)'

print @addfiles

execute(@addfiles)

set @cpu_cnt = @cpu_cnt-1;

end

 Step 4 :- setting-up CPUconfigurations. 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 0;

GO

RECONFIGURE WITH OVERRIDE;

GO