SQL Server function to return the “Xrd” day of a month and year.

USE [Tools]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- select dbo.XrdDay(4,'thursday', 'november', 2018)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2019)	-- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2020)	-- Thanksgiving

-- Must have the day of week and the name of month spelled correctly.
create or alter function [dbo].[XrdDay](
	@xrd int				--3rd, 4th, 5th, etc.
	,@dayname varchar(8)	--'saturday'
	,@month varchar(10)		--'february'
	,@year smallint			--year
)
returns date
as 
begin;
	-- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019;

	declare @Result date=  @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year);	-- get Sunday.
	--select @Result, datename(weekday, @Result);

	--I'm fairly sure this is inefficient, but I can't seem to recall of another way around it.
	--to make it better, we need to calc @sunday+int==dayname in one go, not this while loop.
	while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result );
	--select @Result, datename(weekday, @Result);

	return @Result;
end;

Agent Alerts Not Configured – Brent Ozar Unlimited®

Here is Brent Ozar’s SQL script to add important alerts: via Agent Alerts Not Configured – Brent Ozar Unlimited®

This script sets up SQL Server Agent alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors.

Do a search & replace in this code and change ‘The DBA Team’ to the name of your SQL Agent operator – and remember, always use email distribution lists for your operator emails, not individual peoples’ emails.  Someday – you’re going to go on vacation, and you want other people to get notified when you’re gone.

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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @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'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
 @message_id=823,
 @severity=0,
 @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'Error Number 823', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
 @message_id=824,
 @severity=0,
 @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'Error Number 824', @operator_name=N'The DBA Team', @notification_method = 7;
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
 @message_id=825,
 @severity=0,
 @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'Error Number 825', @operator_name=N'The DBA Team', @notification_method = 7;
 GO

SQL Server: What is Data Warehousing?

A data warehouse is the main repository of a company’s historical data.

Data warehouses can contain a variety of data that presents a coherent picture of the business’s conditions at a point in time.

The main factor of using a data warehouse is that analysts can perform complex queries (data mining) on the information without slowing down the production database servers.

TempDB Best Practices

  • TempDB should have as many data files as there are cores available to SQL Server, but only up to 8 data files. Too many TempDB data files can cause contention. Recommendation: Create 8 files and test from there on.
  • TempDB should only have 1 log file. Extra log files will improve performance as they are written to sequentially.
  • The data files for TempDB should all be the same size.
  • TempDB data files and TempDB log files should be on separate disks.
  • TempDB files should be on the fastest disks.