DBA Ramblings
random things about sql server
Wednesday, July 3, 2013
Sunday, October 2, 2011
Installation Scripts
-- 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
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
Tuesday, July 27, 2010
SQL Server System Documentation
So I've taken the System Documentation sample written by Jorge Segarra here, and have updated it to include more details. You can find it here. Leave a comment if you find it useful!
Thursday, July 1, 2010
Review: Professional SQL Server 2008 Internals and Troubleshooting
Most of the SQL books I own are related to Administration and specific SQL technologies (such as Reporting Services, Clustering etc). I generally wouldn't pick up a book on troubleshooting, most of the time I can find the information I need on the Internet (I like to think of myself as a fairly good Google-r). However I saw the book Professional SQL Server 2008 Internals and Troubleshooting being promoted around the SQL community, so I checked it out and thought it may actually be a good read. I figured that even if it ended up a paperweight on my desk, I could at least claim the expense of the book as a tax deduction!
Well let me say that this is probably the best money I have spent on a book in a very long time. I only wish I had read it sooner.
The chapter I loved the most was the one on locking and latches. At my previous role we had nothing but trouble with locking and latches. I like to think I did my best at troubleshooting the issues, but there was a point where I was struggling with working out the root cause of the problem. I searched furtively for any information online regarding locks and latches, but there wasn't anything out there that I found useful, or informative enough. This chapter explained everything I wanted to know in detail about how locks and latches work. I have since moved on to a new role where there aren't these sorts of issues, but it is handy to have the knowledge there so when I do run into this sort of problem again I can understand what SQL Server is doing and maybe have a better chance of fixing it!
The other topics covered in this book are also very informative and detailed. I haven't come across any other content before that gives this much information about the underlying components and how the internals of SQL server work. It also has many chapters dedicated to various (free!) tools you can use to help troubleshoot SQL Server.
I don't really know what else to say about this book, apart from I highly recommend you go out and get a copy right this instant. If you have ever been interested in knowing what goes on "under the covers" of SQL Server, how to diagnose problems and actually understand what is going on, then this book is for you. Arming yourself with this knowledge will certainly help when you run into problems and need to do troubleshooting on your SQL Server, rather than try search the Internet for information that just isn't out there.
You can find the details of the book and links to buy it at its website: http://sqlservertroubleshooting.com/
Well let me say that this is probably the best money I have spent on a book in a very long time. I only wish I had read it sooner.
The chapter I loved the most was the one on locking and latches. At my previous role we had nothing but trouble with locking and latches. I like to think I did my best at troubleshooting the issues, but there was a point where I was struggling with working out the root cause of the problem. I searched furtively for any information online regarding locks and latches, but there wasn't anything out there that I found useful, or informative enough. This chapter explained everything I wanted to know in detail about how locks and latches work. I have since moved on to a new role where there aren't these sorts of issues, but it is handy to have the knowledge there so when I do run into this sort of problem again I can understand what SQL Server is doing and maybe have a better chance of fixing it!
The other topics covered in this book are also very informative and detailed. I haven't come across any other content before that gives this much information about the underlying components and how the internals of SQL server work. It also has many chapters dedicated to various (free!) tools you can use to help troubleshoot SQL Server.
I don't really know what else to say about this book, apart from I highly recommend you go out and get a copy right this instant. If you have ever been interested in knowing what goes on "under the covers" of SQL Server, how to diagnose problems and actually understand what is going on, then this book is for you. Arming yourself with this knowledge will certainly help when you run into problems and need to do troubleshooting on your SQL Server, rather than try search the Internet for information that just isn't out there.
You can find the details of the book and links to buy it at its website: http://sqlservertroubleshooting.com/
Tuesday, June 29, 2010
Copy and Restore Job Errors with Log Shipping
Hopefully this can help some other people out, I couldn't find much on this error when I got it, and after a bit of digging I was able to sort out the problem.
I set up log shipping for a database in SQL 2008, very simple set up, just following the SSMS GUI. The backup job would run fine, but the copy and restore jobs on the secondary server kept failing.
This was the output of the copy job:
Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 10.0.0.0, File Version = 10.0.1600.22 ((SQL_PreRelease).080709-1414 )]
Microsoft Corporation. All rights reserved.
2010-06-29 09:41:52.53 ----- START OF TRANSACTION LOG COPY -----
2010-06-29 09:41:52.80 *** Error: Could not retrieve copy settings for secondary ID '[removed]'.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.81 *** Error: The specified agent_id [removed] or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2010-06-29 09:41:52.82 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.82 *** Error: The specified agent_id [removed] or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2010-06-29 09:41:52.83 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.84 *** Error: The specified agent_id [removed] or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2010-06-29 09:41:52.84 ----- END OF TRANSACTION LOG COPY -----
Exit Status: 1 (Error)
As you can see, the same error message was the Error 32016: The specified agent_id %s or agent_type %d do not form a valid pair for log shipping monitoring processing.
I ended up doing a trace on the primary server whilst running the job, and was able to see that the error was being thrown by this code:
if (sys.fn_MSvalidatelogshipagentid(@agent_id, @agent_type) = 0)
begin
select @agent_idstring = cast(@agent_id as sysname)
raiserror(32016, 16, 1, @agent_idstring, @agent_type)
return 1
end
The function sys.fn_MSvalidatelogshipagentid returns either 1 or 0 using the following code:
return
case
when
((@agent_type = 0) and
exists (select * from msdb.dbo.log_shipping_monitor_primary
where primary_id = @agent_id))
then 1
when
((@agent_type in (1,2)) and
exists (select * from msdb.dbo.log_shipping_monitor_secondary
where secondary_id = @agent_id))
then 1
else 0
end
Now as I knew the agent type was either 1 (copy) or 2 (restore), so I looked at the table msdb.dbo.log_shipping_monitor_secondary on the primary server, which was empty, hence the function returning 0.
After a bit of banging my head on the desk as to why this function was running on the primary server, as that table is only meant to be populated on the secondary server, I had a look at the job to see how it was calling the sqllogship.exe program:
"D:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Copy [ID_removed] -server SRV01
where SRV01 is the name of the primary server.
So I changed the -server parameter over to the secondary server name (SRV02), re-ran the job, and it worked!
Reading the BOL about sqllogship.exe, it states for the -server parameter:
For -copy or -restore, instance_name must be the name of a secondary server in a log shipping configuration.
Which makes sense why the jobs now ran OK as they were now using the correct parameter value.
To summarize: If you are getting error 32016 The specified agent_id %s or agent_type %d do not form a valid pair for log shipping monitoring processing check the command of the Copy and Restore jobs, that the -server parameter is set to the secondary server name.
The weird thing is that log shipping was setup via SSMS, I just filled in the details and the jobs were created automatically - so SQL Server itself had put the parameter of SRV01 there on its own accord. I can't see how something I did made that parameter be the wrong value. Maybe I did do something, but I did recreate the log shipping several times and double-checked what information I had put in.
I'd be interested to hear if anyone else has had this issue where the primary server name ends up in the copy and restore jobs instead of the secondary server name, and how you set up your log shipping.
I set up log shipping for a database in SQL 2008, very simple set up, just following the SSMS GUI. The backup job would run fine, but the copy and restore jobs on the secondary server kept failing.
This was the output of the copy job:
Microsoft (R) SQL Server Log Shipping Agent
[Assembly Version = 10.0.0.0, File Version = 10.0.1600.22 ((SQL_PreRelease).080709-1414 )]
Microsoft Corporation. All rights reserved.
2010-06-29 09:41:52.53 ----- START OF TRANSACTION LOG COPY -----
2010-06-29 09:41:52.80 *** Error: Could not retrieve copy settings for secondary ID '[removed]'.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.81 *** Error: The specified agent_id [removed] or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2010-06-29 09:41:52.82 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.82 *** Error: The specified agent_id [removed] or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2010-06-29 09:41:52.83 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2010-06-29 09:41:52.84 *** Error: The specified agent_id [removed] or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2010-06-29 09:41:52.84 ----- END OF TRANSACTION LOG COPY -----
Exit Status: 1 (Error)
As you can see, the same error message was the Error 32016: The specified agent_id %s or agent_type %d do not form a valid pair for log shipping monitoring processing.
I ended up doing a trace on the primary server whilst running the job, and was able to see that the error was being thrown by this code:
if (sys.fn_MSvalidatelogshipagentid(@agent_id, @agent_type) = 0)
begin
select @agent_idstring = cast(@agent_id as sysname)
raiserror(32016, 16, 1, @agent_idstring, @agent_type)
return 1
end
The function sys.fn_MSvalidatelogshipagentid returns either 1 or 0 using the following code:
return
case
when
((@agent_type = 0) and
exists (select * from msdb.dbo.log_shipping_monitor_primary
where primary_id = @agent_id))
then 1
when
((@agent_type in (1,2)) and
exists (select * from msdb.dbo.log_shipping_monitor_secondary
where secondary_id = @agent_id))
then 1
else 0
end
Now as I knew the agent type was either 1 (copy) or 2 (restore), so I looked at the table msdb.dbo.log_shipping_monitor_secondary on the primary server, which was empty, hence the function returning 0.
After a bit of banging my head on the desk as to why this function was running on the primary server, as that table is only meant to be populated on the secondary server, I had a look at the job to see how it was calling the sqllogship.exe program:
"D:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Copy [ID_removed] -server SRV01
where SRV01 is the name of the primary server.
So I changed the -server parameter over to the secondary server name (SRV02), re-ran the job, and it worked!
Reading the BOL about sqllogship.exe, it states for the -server parameter:
For -copy or -restore, instance_name must be the name of a secondary server in a log shipping configuration.
Which makes sense why the jobs now ran OK as they were now using the correct parameter value.
To summarize: If you are getting error 32016 The specified agent_id %s or agent_type %d do not form a valid pair for log shipping monitoring processing check the command of the Copy and Restore jobs, that the -server parameter is set to the secondary server name.
The weird thing is that log shipping was setup via SSMS, I just filled in the details and the jobs were created automatically - so SQL Server itself had put the parameter of SRV01 there on its own accord. I can't see how something I did made that parameter be the wrong value. Maybe I did do something, but I did recreate the log shipping several times and double-checked what information I had put in.
I'd be interested to hear if anyone else has had this issue where the primary server name ends up in the copy and restore jobs instead of the secondary server name, and how you set up your log shipping.
Subscribe to:
Posts (Atom)