Thursday, December 22, 2022
Using Variables To Update and Increment the Value by 1
UPDATE accounts2 SET id = NULL
GO
DECLARE @id INT
SET @id = 0
UPDATE accounts2
SET @id = id = @id + 10
GO
SELECT * FROM accounts2
GO
if you do not have any option to use Idenity column , the above will be helpful
Load Dim_date ( i have loaded 2023)
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'DimDate'))
BEGIN
DROP TABLE DimDate
END
go
GO
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDate] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeek] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE
)
GO
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2023' --Starting value of Date Range
DECLARE @EndDate DATETIME = '12/31/2023' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign various parts of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN
/*Begin day of week logic*/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
/* Check for Change in Year of the Current date if Year changed then change
Variable value*/
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
/*End day of week logic*/
/* Populate Your Dimension Table with values*/
INSERT INTO [dbo].[DimDate]
SELECT
CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,101) as FullDate,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeek,
@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR,
DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR,
DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0),
@CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR,
DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) +
CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
@CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
(DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1,
@CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY,
@CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY,
@CurrentDate))) AS LastDayOfYear
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
/********************************************************************************************/
go
SELECT * FROM [dbo].[DimDate]
INSERT INTO dbo.DimDate
(
DateKey,
Date,
FullDate,
DayOfMonth,
DayName,
DayOfWeek,
DayOfWeekInMonth,
DayOfWeekInYear,
DayOfQuarter,
DayOfYear,
WeekOfMonth,
WeekOfQuarter,
WeekOfYear,
Month,
MonthName,
MonthOfQuarter,
Quarter,
QuarterName,
Year,
YearName,
MonthYear,
MMYYYY,
FirstDayOfMonth,
LastDayOfMonth,
FirstDayOfQuarter,
LastDayOfQuarter,
FirstDayOfYear,
LastDayOfYear
)
VALUES
( 20231231, -- DateKey - int
'2023-12-31 00:00:00.000', -- Date - datetime
'12/31/2023', -- FullDate - char(10)
'31', -- DayOfMonth - varchar(2)
'Sunday', -- DayName - varchar(9)
'1', -- DayOfWeek - char(1)
'6', -- DayOfWeekInMonth - varchar(2)
'53', -- DayOfWeekInYear - varchar(2)
'14', -- DayOfQuarter - varchar(3)
'365', -- DayOfYear - varchar(3)
'6', -- WeekOfMonth - varchar(1)
'14', -- WeekOfQuarter - varchar(2)
'53', -- WeekOfYear - varchar(2)
'12', -- Month - varchar(2)
'December', -- MonthName - varchar(9)
'3', -- MonthOfQuarter - varchar(2)
'4', -- Quarter - char(1)
'Fourth', -- QuarterName - varchar(9)
'2023', -- Year - char(4)
'CY 2023', -- YearName - char(7)
'Dec-2023 ', -- MonthYear - char(10)
'122023', -- MMYYYY - char(6)
'2023-12-01', -- FirstDayOfMonth - date
'2023-12-31', -- LastDayOfMonth - date
'2023-10-01', -- FirstDayOfQuarter - date
'2023-12-31', -- LastDayOfQuarter - date
'2023-01-01', -- FirstDayOfYear - date
'2023-12-31' -- LastDayOfYear - date
)
--another example
--INSERT INTO [dbo].[Dim_Date]
-- ([Date_Type]
-- ,[Full_Date]
-- ,[Full_Date_BI]
-- ,[Year_Month_Number]
-- ,[Year_Number]
-- ,[Day_Of_Week_Number]
-- ,[Day_Of_Month_Number]
-- ,[Day_Of_Quarter_Number]
-- ,[Day_Of_Year_Number]
-- ,[Day_Name]
-- ,[Week_Of_Month_Number]
-- ,[Week_Of_Quarter_Number]
-- ,[Week_Of_Year_Number]
-- ,[Month_Of_Quarter_Number]
-- ,[Month_Of_Year_Number]
-- ,[Month_Name]
-- ,[Abbr_Month_Name]
-- ,[Quarter_Of_Year_Number]
-- ,[Quarter_Name]
-- ,[is_Today]
-- ,[is_Current_Week]
-- ,[is_Last_Week]
-- ,[is_Current_Month]
-- ,[is_Last_Month]
-- ,[is_Last_Day_Of_Month]
-- ,[is_Last_30_Days]
-- ,[is_Last_28_Days]
-- ,[is_Last_12_Weeks]
-- ,[is_Last_12_Months]
-- ,[is_Holiday]
-- ,[Sales_Start_Time]
-- ,[Sales_End_Time]
-- ,[Support_Start_Time]
-- ,[Support_End_Time]
-- ,[Dialer_Start_Time]
-- ,[Dialer_End_Time]
-- ,[Lead_Business_Day]
-- ,[Sale_Business_Day]
-- ,[Shipping_Day_Num]
-- ,[Insert_Audit_Key]
-- ,[Update_Audit_Key])
SELECT
'Date' AS Date_type
, [Date] AS FULL_date
,[Date] AS FULL_date_BI
,CASE
WHEN [MonthName] = 'January' THEN '202301'
WHEN [MonthName] = 'February' THEN '202302'
WHEN [MonthName] = 'March' THEN '202303'
WHEN [MonthName] = 'April' THEN '202304'
WHEN [MonthName] = 'May' THEN '202305'
WHEN [MonthName] = 'June' THEN '202306'
WHEN [MonthName] = 'July' THEN '202307'
WHEN [MonthName] = 'August' THEN '202308'
WHEN [MonthName] = 'September' THEN '202309'
WHEN [MonthName] = 'October' THEN '202310'
WHEN [MonthName] = 'November' THEN '202311'
WHEN [MonthName] = 'December' THEN '202312'
ELSE NULL
END AS Year_month_Number,
[Year] AS Year_Number,
[Dayofweek] AS Day_Of_Week_Number
,[dayofmonth] AS Day_of_Month_number
,dayofQuarter AS Day_of_Quarter_number---? Have to fix this
,[DayOFyear] AS day_of_year_number
,[dayname] AS Day_name
,[Weekofmonth] AS week_of_month_number
,[WeekofQuarter] AS week_of_Quarter_number
,WeekOfyear AS Week_of_year_number
,MonthofQuarter AS Month_Of_Quarter_number
,[Month] AS Month_of_year_number
,[MonthName] AS Month_name
,
CASE
WHEN [MonthName] = 'January' THEN 'Jan'
WHEN [MonthName] = 'February' THEN 'Feb'
WHEN [MonthName] = 'March' THEN 'Mar'
WHEN [MonthName] = 'April' THEN 'Apr'
WHEN [MonthName] = 'May' THEN 'May'
WHEN [MonthName] = 'June' THEN 'Jun'
WHEN [MonthName] = 'July' THEN 'Jul'
WHEN [MonthName] = 'August' THEN 'Aug'
WHEN [MonthName] = 'September' THEN 'Sep'
WHEN [MonthName] = 'October' THEN 'Oct'
WHEN [MonthName] = 'November' THEN 'Nov'
WHEN [MonthName] = 'December' THEN 'Dec'
ELSE NULL
END AS Abbr_month_name
,[Quarter] AS Quarter_of_Year_name
,Quartername AS Quarter_name
,0 AS is_today
,0 AS is_current_week
,0 AS is_last_week
,0 AS is_current_Month
,0 AS is_last_month
,0 AS is_last_day_of_month --case add here ---ned to fix case when month =02 and date 28 then 1
---like that addd case
,0 AS is_last_30_Days
,0 AS is_last_28_days
,0 AS is_last_12_weeks
,0 AS is_last_12_months
,0 as is_holiday
, CASE
WHEN [DayName]= 'Sunday' THEN '10:00:00'
WHEN [DayName]= 'Saturday' THEN '10:00:00'
WHEN [DayName]= 'Friday' THEN '08:00:00'
WHEN [DayName]= 'Thursday' THEN '08:00:00'
WHEN [DayName]= 'Wednesday' THEN '08:00:00'
WHEN [DayName]= 'Tuesday' THEN '08:00:00'
WHEN [DayName]= 'Monday' THEN '08:00:00'
ELSE NULL
END AS sales_start_Time
, CASE
WHEN [DayName]='Sunday' THEN '19:00:00'
WHEN [DayName]='Saturday' THEN '19:00:00'
WHEN [DayName]='Friday' THEN '23:00:00'
WHEN [DayName]='Thursday' THEN '23:00:00'
WHEN [DayName]='Wednesday' THEN '23:00:00'
WHEN [DayName]='Tuesday' THEN '23:00:00'
WHEN [DayName]='Monday' THEN '23:00:00'
ELSE NULL
END AS sales_End_Time
, CASE
WHEN [DayName]='Sunday' THEN '19:00:00'
WHEN [DayName]='Saturday' THEN '19:00:00'
WHEN [DayName]='Friday' THEN '23:00:00'
WHEN [DayName]='Thursday' THEN '23:00:00'
WHEN [DayName]='Wednesday' THEN '23:00:00'
WHEN [DayName]='Tuesday' THEN '23:00:00'
WHEN [DayName]='Monday' THEN '23:00:00'
ELSE NULL
END AS Support_Start_Time
, CASE
WHEN [DayName]= 'Sunday' THEN '19:00:00'
WHEN [DayName]= 'Saturday' THEN '19:00:00'
WHEN [DayName]= 'Friday' THEN '23:00:00'
WHEN [DayName]= 'Thursday' THEN '23:00:00'
WHEN [DayName]= 'Wednesday' THEN '23:00:00'
WHEN [DayName]= 'Tuesday' THEN '23:00:00'
WHEN [DayName]= 'Monday' THEN '23:00:00'
ELSE NULL
END AS Support_End_Time
, CASE
WHEN [DayName]= 'Sunday' THEN '19:00:00'
WHEN [DayName]= 'Saturday' THEN '19:00:00'
WHEN [DayName]= 'Friday' THEN '23:00:00'
WHEN [DayName]= 'Thursday' THEN '23:00:00'
WHEN [DayName]= 'Wednesday' THEN '23:00:00'
WHEN [DayName]= 'Tuesday' THEN '23:00:00'
WHEN [DayName]= 'Monday' THEN '23:00:00'
ELSE NULL
END AS Dialer_Start_Time
, CASE
WHEN [DayName]= 'Sunday' THEN '19:00:00'
WHEN [DayName]= 'Saturday' THEN '19:00:00'
WHEN [DayName]= 'Friday' THEN '23:00:00'
WHEN [DayName]= 'Thursday' THEN '23:00:00'
WHEN [DayName]= 'Wednesday' THEN '23:00:00'
WHEN [DayName]= 'Tuesday' THEN '23:00:00'
WHEN [DayName]= 'Monday' THEN '23:00:00'
ELSE NULL
END AS Dialer_End_Time
, CASE
WHEN [DayName]= 'Sunday' THEN '0.39'
WHEN [DayName]= 'Saturday' THEN '0.46'
WHEN [DayName]= 'Friday' THEN '0.69'
WHEN [DayName]= 'Thursday' THEN '0.75'
WHEN [DayName]= 'Wednesday' THEN '0.83'
WHEN [DayName]= 'Tuesday' THEN '0.84'
WHEN [DayName]= 'Monday' THEN '1.00'
ELSE NULL
END AS Lead_Business_day
, CASE
WHEN [DayName]= 'Sunday' THEN '0.30'
WHEN [DayName]= 'Saturday' THEN '0.70'
WHEN [DayName]= 'Friday' THEN '0.71'
WHEN [DayName]= 'Thursday' THEN '0.84'
WHEN [DayName]= 'Wednesday' THEN '0.83'
WHEN [DayName]= 'Tuesday' THEN '1,00'
WHEN [DayName]= 'Monday' THEN '0.06'
ELSE NULL
END AS Lead_Business_day
--,Shipping_day_num --have to fix this ---increment need to fix this
, 681161 AS Insert_audit_key
,-1 AS Update_Audit_key
FROM AuditDB.[dbo].[DimDate]
Wednesday, December 21, 2022
USE [AuditDB]
GO
/****** Object: Table [dbo].[NK_BlockingReport] Script Date: 3/8/2022 12:41:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BlockingReport](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ObjectName] [sysname] NULL,
[BlockingObjectName] [sysname] NULL,
[StatementText] [VARCHAR](MAX) NULL,
[BlockingStatementText] [VARCHAR](MAX) NULL,
[SessionID] [INT] NULL,
[BlockingSessionID] [INT] NULL,
[Status] [VARCHAR](50) NULL,
[BlockingStatus] [VARCHAR](50) NULL,
[DateCreated] [DATETIME] NULL,
[DatabaseName] [VARCHAR](100) NULL,
[CPU] [BIGINT] NULL,
[RunningTime] [INT] NULL,
[RuuningFrom] [VARCHAR](800) NULL,
[RuuningBy] [VARCHAR](800) NULL,
[ProgramName] [VARCHAR](100) NULL,
[LoginName] [VARCHAR](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [AuditDB]
GO
/****** Object: Table [dbo].[Deadlock] Script Date: 3/8/2022 12:42:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeadlockReport](
[RecordId] [INT] IDENTITY(1,1) NOT NULL,
[AlertTime] [DATETIME] NOT NULL,
[DeadlockGraph] [XML] NULL,
[Notified] [INT] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RecordId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Deadlock] ADD CONSTRAINT [DF_deadlock_flag] DEFAULT ((0)) FOR [Notified]
GO
Alerts that should have in sql prod environments
Alert when long running qureies are found--every 2 hrs
Alert Current SQL jobs are running -daily 8am
SendJobFailureReport
AlertSendIndexFragmentationdetails
AvailabilityGroupsInfo
Check Database Status
Check For Missing Backups
CheckAlwaysonSyncStatus-Every5minutes
Collect_Database_Size_Info_Daily1am
IndexFragmentationdetails-Shop-Every1Hour
Notify when AG Replicas Disconnect
SendSpaceAlert
SQLRestart Notify
TrackAvailabilityLatenc
Alert Index fragmentation report
DECLARE
@Document NVARCHAR(MAX) ,
@DocumentHead NVARCHAR(MAX) ,
@DocumentTail NVARCHAR(MAX) ,
@TableHead NVARCHAR(MAX) ,
@TableBody NVARCHAR(MAX) ,
@TableTail NVARCHAR(MAX) ,
@page_count_minimum SMALLINT ,
@fragmentation_minimum INT;
SET @page_count_minimum =1000;
SET @fragmentation_minimum = 30;
SET @DocumentHead = N'' + N'' + N'' + N''
+ N''
+ N'
';
SELECT @TableBody = ( SELECT ROW_NUMBER() OVER ( ORDER BY avg_fragmentation_in_percent DESC )
% 2 AS [TRRow] ,
sys.objects.name AS [TD] ,
sys.indexes.name AS [TD] ,
CONVERT(VARCHAR(10), avg_fragmentation_in_percent, 121) AS [TD] ,
page_count AS [TD]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,
NULL, NULL,
'LIMITED')
INNER JOIN sys.objects ON sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
INNER JOIN sys.indexes ON sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND objects.is_ms_shipped = 0
AND sys.dm_db_index_physical_stats.index_id > 0 and sys.indexes.name<>'NIX_AccountTicketNote_AccountTicketID_TicketStateID'--Excluded This particular index Condition on may5th 2016
AND page_count > @page_count_minimum
ORDER BY avg_fragmentation_in_percent DESC
FOR
XML RAW('tr') ,
ELEMENTS
);
IF @TableBody IS NULL
BEGIN
RETURN;
END;
ELSE
BEGIN
SET @TableBody = REPLACE(@TableBody, '_x0020_', SPACE(1));
SET @TableBody = REPLACE(@TableBody, '_x003D_', '=');
SET @TableBody = REPLACE(@TableBody, '1 ','');
SET @TableBody = REPLACE(@TableBody, '0 ', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients ='abc@abc',
@subject = 'Average Index Fragmentation with above 30 Percent',
@body = @Document,
@body_format = 'HTML';
END
END
' + N'Below are the Indexes in Workbench2Prod Database with Average Fragmentation above 30 Percent' + N'
'; SET @DocumentTail = ''; SET @TableHead = N'| TableName | ' + N'IndexName | ' + N'AverageFragmentationPercent | ' + N'PageCount | ' + N'
|---|
Capture blocking
--Table:
/****** Object: Table [dbo].[NK_BlockingReport] Script Date: 12/21/2022 12:25:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NK_BlockingReport](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[ObjectName] [sysname] NULL,
[BlockingObjectName] [sysname] NULL,
[StatementText] [VARCHAR](MAX) NULL,
[BlockingStatementText] [VARCHAR](MAX) NULL,
[SessionID] [INT] NULL,
[BlockingSessionID] [INT] NULL,
[Status] [VARCHAR](50) NULL,
[BlockingStatus] [VARCHAR](50) NULL,
[DateCreated] [DATETIME] NULL,
[DatabaseName] [VARCHAR](100) NULL,
[CPU] [BIGINT] NULL,
[RunningTime] [INT] NULL,
[RuuningFrom] [VARCHAR](800) NULL,
[RuuningBy] [VARCHAR](800) NULL,
[ProgramName] [VARCHAR](100) NULL,
[LoginName] [VARCHAR](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--SP:
/****** Object: StoredProcedure [dbo].[NK_SP_InsertBlockingRecords] Script Date: 12/21/2022 12:24:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Schedule Job to run sp and send email when alert happens
ALTER PROC [dbo].[NK_SP_InsertBlockingRecords]
AS
INSERT INTO dbo.NK_BlockingReport (ObjectName,BlockingObjectName, StatementText,BlockingSessionID,BlockingStatementText,
BlockingStatus,DateCreated,[Status],DatabaseName,CPU,RunningTime,RuuningFrom,RuuningBy,ProgramName,LoginName)
SELECT W1.ObjectName AS ObjectName,
W2.ObjectName AS BlockingObjectName,
W1.Statement_Text AS BlockedStatementText,
W2.sessionid AS BlockingSessionID,
W2.Statement_Text AS BlockingStatementText,
W2.status AS BlockingStatus,
GETDATE() AS DateCreated,
w1.[Status] AS CurrentStatus,
w1.DatabaseName ,
w1.[CPU_time] ,
w2.[RunningMinutes] ,
w2.[RunningFrom] ,
w1.[RunningBy] ,
w1.[Program_Name] ,
w1.[Login_Name]
FROM NK_SP_Active W1
INNER JOIN NK_SP_Active W2
ON W1.BlockingWith > 0
AND W1.BlockingWith = W2.sessionid;
job:
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
EXEC NK_SP_InsertBlockingRecords
END
ELSE
BEGIN
RETURN
END
SendJobFailureReport
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
DECLARE @Document NVARCHAR(MAX)
, @DocumentHead NVARCHAR(MAX)
, @DocumentTail NVARCHAR(MAX)
, @TableHead NVARCHAR(MAX)
, @TableBody NVARCHAR(MAX)
, @TableTail NVARCHAR(MAX)
, @Now DATETIME
SET @DocumentHead = N''
+ N''
+ N''
+ N''
+ N''
+ N'
';
SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow],
T1.server AS [TD],
SUBSTRING(T2.name,1,140) AS [TD],
msdb.dbo.agent_datetime(run_date, run_time) as [TD],
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS [TD],
T1.message AS [TD]
FROM msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1, 4) AND T1.step_id != 0 AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
FOR XML RAW('tr'), ELEMENTS);
--SELECT @TableBody
IF @TableBody IS NULL
BEGIN
PRINT 'No Jobs failed';
RETURN;
END
ELSE
BEGIN
-- Replace the entity codes and row numbers
SET @TableBody = Replace(@TableBody, '_x0020_', space(1))
SET @TableBody = Replace(@TableBody, '_x003D_', '=')
SET @TableBody = Replace(@TableBody, '1 ', '')
SET @TableBody = Replace(@TableBody, '0 ', '');
SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default'
, @recipients= 'ABC@ABC.com'
, @subject = 'Failed SQL Server Jobs on SQL'
, @body = @Document
, @body_format = 'HTML';
END
END;
ELSE
BEGIN
RETURN
END
' + N'The following Jobs failed in FPSQL1Listener SQL Server' + N'
'; SET @DocumentTail = ''; SET @TableHead = N'| Server Name | ' + N'Job Name | ' + N'Job Failed DateTime | ' + N'Status | ' + N'Error Message | ' + N'
|---|
Alert when a login is created or dropped
ALTER TRIGGER LoginCreateDropTrigger ON ALL SERVER
FOR CREATE_LOGIN, DROP_LOGIN
AS
BEGIN
DECLARE @mailBody Nvarchar(MAX);
DECLARE @data XML = EVENTDATA();
Declare @Mailsubject NVarchar(MAX) = 'Someone Created or Dropped a Login on SOX Server.As this is a SOX server, please create a ticket:' +@@SERVERNAME
SET @mailBody = 'A new login was created on SQL Server: ' + @@SERVERNAME + '
' +
'By user: ' + ISNULL(@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'), 'Null Login' ) + '
' + +
'The TSql executed to do this was: ' +
ISNULL(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 'Null SQL ' )+ '
' +
'At: ' + CONVERT(nvarchar, getdate(), 120) + '
' +
'Please verify why this login was created and create a ticket if there is not' + '
' ;
Declare @bodyNew Nvarchar (Max) = Convert(Nvarchar(MAX),@data)
Declare @SubjectNew Varchar(max)= 'As this is a SOX Server, Please create a Ticket'+CONVERT(VARCHAR(12),GETDATE(),107)
EXECUTE MSDB.DBO.sp_send_dbmail
@profile_name ='SQLMail' ,
@recipients = 'ABC @ABC.com', --this should be a distro
@importance = 'High',
@Subject=@Mailsubject,
@body = @mailBody,
@body_format = 'HTML'
END
----DROP TRIGGER [LoginCreateDropTrigger] ON ALL SERVER
go
USE [master]
CREATE LOGIN [TestNaresh] WITH PASSWORD=N'P@SSW)rd1@#$'
MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
DROP LOGIN [TestNaresh]
go
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
Tuesday, December 20, 2022
How to stop and start DBmail
recently i was working on sending an email from the output of table and found below error:
Msg 14641, Level 16, State 1, Procedure sp_send_dbmail, Line 81
Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail.
and i was able to start db mail and send emal
--START
USE msdb
GO
EXEC dbo.sysmail_start_sp
go
--STOP
USE msdb
GO
EXEC dbo.sysmail_stop_sp
Monday, December 19, 2022
Index rebuild online
DECLARE @page_count_minimum smallint
DECLARE @fragmentation_minimum float
SET @page_count_minimum =10
SET @fragmentation_minimum = 10
SELECT
'ALTER INDEX ' + ' [' +sys.indexes.name + '] ' + 'On'+ ' ' + sys.objects.name + ' REBUILD WITH (ONLINE = ON)' as b,sys.objects.name,
avg_fragmentation_in_percent AS frag,
page_count AS page_count,
sys.dm_db_index_physical_stats.object_id AS objectid,
partition_number AS partitionnum
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
inner join sys.objects
on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id
inner join sys.indexes
on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
WHERE avg_fragmentation_in_percent > @fragmentation_minimum
AND sys.dm_db_index_physical_stats.index_id > 0
AND page_count > @page_count_minimum
ORDER BY 2
go
-- can filter top 100 or select only table names by joining sysojbects and can also get schema name by joining with schemas
select s.name as schema_name, s.schema_id, u.name as schema_owner from sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id order by s.name
Rebuild index on very Large tables 1 TB table with 2K tables
Rebuild index on very Large tables 1 TB table with 2K tables
I had a situation where rebuilding the indexes ran for 1 day and filled the disk with 1.5TB.
also splitting tables of a very large db to do every nighht if we have a windows
and the log backups were taking ever and not truncating the log..what helped me to rebuild indexes and not to fill disk is below
- changed db to bulk recovery model from full
- rebuild indexes
- changed db to FULL recovery model
UPDATE STATS WITH FULL SCAN is a must run when u migrate or upgrade SQL SERVER
We had some performance issues after migrating SQL Server from windows server 2012 to windows server 2019 and after analysis, I found that I should do
sp_msforeachtable 'UPDATE STATISTICS? WITH FULLSCAN'
or do individual tables
This helped....
If you use FULLSCAN, it will read the entire table.
Question: When do you recommend using updated stats with FULLSCAN rather than default sampled?
Answer: When you know there is skew in the column.
Note: Skewed columns are columns in which the data is not evenly distributed among the rows.
Updating statistics ensures that queries compile with up-to-date statistics. Updating statistics via any process may cause query plans to recompile automatically.
Subscribe to:
Posts (Atom)