-- Get configuration values for instance
SELECT name, value, value_in_use, [description]
FROM sys.configurations
ORDER BY name;
-- Set max server memory = 59000MB for the server
-- (example value only)
EXEC sp_configure 'max server memory (MB)', 59000;
GO
RECONFIGURE;
GO
-- Some suggested Max Server Memory settings
-- Physical RAM Max Server Memory Setting
-- 4GB 3200
-- 6GB 4800
-- 8GB 6200
-- 16GB 13000
-- 24GB 20500
-- 32GB 28000
-- 48GB 44000
-- 64GB 59000
-- 72GB 67000
-- 96GB 90000
-- Enable optimize for ad-hoc workloads
-- (new in SQL Server 2008)
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO
-- Enable backup compression by default
-- (new in SQL Server 2008 Enterprise Edition)
-- (added to SQL Server 2008 R2 Standard Edition
EXEC sp_configure 'backup compression default', 1;
GO
RECONFIGURE;
GO
-- Set MAXDOP = 1 for the server
-- Depends on workload and wait stats
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE;
GO
-- Enable remote DAC connections
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
-- Configure Error Log to keep 30 logs
USE [master]
GO
DECLARE @NumberOfLogs int
SET @NumberOfLogs = 30
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, @NumberOfLogs
GO
-- Setup job to cycle error log daily
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Cycle Errorlog',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'Cycle SQL Server Errorlog',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Cycle Errorlog'--, @server_name = N'(LOCAL)'
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Cycle Errorlog', @step_name=N'Execute Cycle Errorlog',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_cycle_errorlog;',
@database_name=N'master',
@flags=4
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Cycle Errorlog',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'Cycle SQL Server Errorlog',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Cycle Errorlog', @name=N'Schedule Cycle Errorlog',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20100101,
@active_end_date=99991231,
@active_start_time=235930,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO
-- Configure the default mail profile, account and SQL Agent settings for Database Mail
USE [msdb]
GO
EXECUTE sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXECUTE sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
DECLARE @ProfileID int
, @AccountID int
, @ReturnCode int = 0
-- Update all variables below this line to confgure Database Mail as needed
, @ServerName sysname = N''
DECLARE @AccountDescription nvarchar(256) = N'The default Database Mail account'
, @AccountName sysname = N'Public Mail Account'
, @DisplayName nvarchar(128) = @ServerName + ' SQL Server'
, @EmailAddress nvarchar(128) = N'email@domain.com'
, @FailSafeOperator nvarchar(255) = N'DBA'
, @MailServer sysname = N'mail.server.com'
, @ReplyToAddress nvarchar(128) = N'email@domain.com'
, @ProfileDescription nvarchar(256) = N'The default Database Mail profile'
, @ProfileName sysname = N'Public Mail Profile'
BEGIN TRY
BEGIN TRANSACTION
-- Create the default mail profile
EXECUTE @ReturnCode = dbo.sysmail_add_profile_sp @profile_name = @ProfileName, @description = @ProfileDescription, @profile_id = @ProfileID OUTPUT
-- Create the default mail account
EXECUTE @ReturnCode = dbo.sysmail_add_account_sp @account_name = @AccountName, @email_address = @EmailAddress, @display_name = @DisplayName,
@replyto_address = @ReplyToAddress, @description = @AccountDescription, @mailserver_name = @MailServer, @use_default_credentials = 1,
@account_id = @AccountID OUTPUT
-- Add the default account to the default profile
EXECUTE @ReturnCode = dbo.sysmail_add_profileaccount_sp @profile_id = @ProfileID, @account_id = @AccountID, @sequence_number = 1
-- Set the default Database Mail profile to be our shiny new profile
EXECUTE @ReturnCode = dbo.sysmail_add_principalprofile_sp @principal_id = 0, @profile_id = @ProfileID, @is_default = 1
COMMIT TRANSACTION
-- Configure SQL Server Agent to send emails via Database Mail using the Default Profile
EXECUTE [master].dbo.sp_MSsetalertinfo @failsafeoperator = @FailSafeOperator, @notificationmethod = 1
EXECUTE msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1
EXECUTE [master].dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
EXECUTE [master].dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @ProfileName
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
DECLARE @ErrorMessage nvarchar(2048) = ERROR_MESSAGE()
, @ErrorSeverity int = ERROR_SEVERITY()
RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH
-- Create Operator
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBA',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'email@domain.com',
@category_name=N'[Uncategorized]'
GO
-- Alter tempdb – move and resize
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', NEWNAME = 'tempdev1', FILENAME = 'D:\SQLTempDB\tempdev1.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'D:\SQLTempDB\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev1', SIZE = 4096MB , FILEGROWTH = 1024MB);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', SIZE = 1024MB , FILEGROWTH = 512MB);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev2',
FILENAME = 'D:\SQLTempDB\tempdev2.ndf',
SIZE = 4096MB, FILEGROWTH = 1024MB);
GO
-- Change model database settings
ALTER DATABASE model SET RECOVERY SIMPLE
GO
ALTER DATABASE model
MODIFY FILE (NAME='modeldev', SIZE = 2048MB, FILEGROWTH=1024MB);
GO
ALTER DATABASE model
MODIFY FILE (NAME='modellog', SIZE = 1024MB, FILEGROWTH=512MB);
GO
-- Create Alerts
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'DBA', @notification_method = 7
GO
