Naresh Koudagani SQL Server DBA Blog: 2021

Wednesday, December 1, 2021

parameter sniffing explained well

https://indatawetrust.blog/2011/08/19/making-ssrs-reports-faster-get-rid-of-parameter-sniffing/

Saturday, October 2, 2021

SSIS Date variables tips

---getdate with HH MM Seconds

(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)
+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms", GETDATE()), 4)

---getdate -9 days with HH MM Seconds
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" +(DT_STR, 2, 1252)DATEPART("dd" , DATEADD("Day",-9,GETDATE())), 2)
+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms", GETDATE()), 4)

-----: Todays date
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

---: minues 1 month and minus 1 day:
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("mm",-1,GETDATE())), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , DATEADD("Day",-1,GETDATE())), 2)

---Last 15 minutes:
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)
+ "-" + RIGHT("0" +(DT_STR, 2, 1252)DATEPART("dd" , DATEADD("Day",-9,GETDATE())), 2)
+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2)
+":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", DATEADD("mi",-15,GETDATE())), 2)
+":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms", GETDATE()), 4)

Friday, August 20, 2021

alert index fragmentation

USE [FPAuth]

/****** Object: StoredProcedure [dbo].[AlertSendIndexFragmentationdetails_Job_Step3] Script Date: 8/20/2021 8:06:37 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Proc [dbo].[AlertSendIndexFragmentationdetails_Job_Step3]

BEGIN

Author:Naresh DBA

Date: 04/01/2016

Purpose:Send Index fragmentation details every 1 hour

Paremeters: page_count_minimum = 1000 and fragmentation_minimum = 30

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'<html><head>' + N'<style>' + N'th, td' + N'{ '

+ N' border: solid black 1px;' + N' padding: 1px 5px 1px 5px;'

+ N' font-size: 11pt;' + N'}' + N'</style>' + N'</head>' + N'<body>'

+ N'<h3>'

+ N'Below are the Indexes in FPAuth Database with Average Fragmentation above 30 Percent'

+ N'</h3>';

SET @DocumentTail = '</body></html>';

SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'

+ N'<tr bgcolor=#C0C0C0>' + N' <th>TableName</th>'

+ N' <th>IndexName</th>' + N' <th>AverageFragmentationPercent</th>'

+ N' <th>PageCount</th>' + N'</tr>';

SET @TableTail = '</table>';

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, '<tr><TRRow>1</TRRow>','<tr bgcolor=#C6CFFF>');

SET @TableBody = REPLACE(@TableBody, '<TRRow>0</TRRow>', '');

SELECT @Document = @DocumentHead + @TableHead + @TableBody+ @TableTail + @DocumentTail;

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',

@recipients = 'abc@xyz.com',

@subject = 'Average Index Fragmentation with above 30 Percent',

@body = @Document,

@body_format = 'HTML';

END

Track Blocking and insert in to a table: when blocking happens

---1 create table to log info

USE [AuditDB]

/****** Object: Table [dbo].[NK_BlockingReport] Script Date: 8/20/2021 7:46:13 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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]

---2. Create view:

USE [AuditDB]

/****** Object: View [dbo].[NK_SP_Active] Script Date: 8/20/2021 7:49:26 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[NK_SP_Active]

SELECT

OBJECT_NAME(objectid) AS ObjectName

,SUBSTRING(stateText.text, (statement_start_offset/2)+1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(stateText.text)

ELSE statement_end_offset

END - statement_start_offset)/2) + 1) AS statement_text

,DB_NAME(sess.database_id) AS DatabaseName

,req.cpu_time AS CPU_Time

,DATEDIFF(MINUTE, last_request_start_time, GETDATE()) AS RunningMinutes

,req.Percent_Complete

,sess.HOST_NAME AS RunningFrom

,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy

,sess.session_id AS SessionID

,req.blocking_session_id AS BlockingWith

,req.reads

,req.writes

,sess.[program_name]

,sess.login_name

,sess.status

,sess.last_request_start_time

,req.logical_reads

FROM

sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id

AND sess.is_user_process = 1

CROSS APPLY

sys.dm_exec_sql_text(sql_handle) AS stateText

---3.Create job

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;

---4. Create alert --MANUALY BEST, instaed of script

USE [msdb]

/****** Object: Alert [NK_Report Blocked Processes] Script Date: 8/20/2021 7:44:40 AM ******/

EXEC msdb.dbo.sp_add_alert @name=N'NK_Report Blocked Processes',

@message_id=0,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@notification_message=N'Blocking Occured please look at BlockingReport in AuditDB',

@category_name=N'[Uncategorized]',

@performance_condition=N'MSSQL$DBA_TEST:General Statistics|Processes blocked||>|1',

@job_id=N'6095582e-4202-497b-af3c-1f7842383739'

Thursday, August 12, 2021

send details about Currently running jobs in sql server

IF EXISTS (

SELECT

j.name AS job_name,

ja.start_execution_date,

ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,

Js.step_name

FROM msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

ON ja.job_history_id = jh.instance_id

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

ON ja.job_id = js.job_id

AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id

WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

AND start_execution_date is not null

AND stop_execution_date is null

)

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'<html><head>'

+ N'<style>'

+ N'th, td'

+ N'{ '

+ N' border: solid black 1px;'

+ N' padding: 1px 5px 1px 5px;'

+ N' font-size: 11pt;'

+ N'}'

+ N'</style>'

+ N'</head>'

+ N'<body>'

+ N'<h3>'

+ N'Currently Running Jobs on '+' '+ @@SERVERNAME + ' .'+ 'Please check any long running jobs and Also we should NOT have REBUILD INDEXES,REBUILD STATS, OR FULL BACKUPS running at this moment.' + ' '+ Convert(varchar(19),getdate())

+ N'</h3>';

SET @DocumentTail = '</body></html>';

SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'

+ N'<tr bgcolor=#C0C0C0>'

+ N' <th>Server Name</th>'

+ N' <th>Job Name</th>'

+ N'</tr>';

SET @TableTail = '</table>';

SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By run_date) % 2 As [TRRow],

@@SERVERNAME AS TD,

j.name AS job_name

FROM msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh

ON ja.job_history_id = jh.instance_id

JOIN msdb.dbo.sysjobs j

ON ja.job_id = j.job_id

JOIN msdb.dbo.sysjobsteps js

ON ja.job_id = js.job_id

AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id

WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)

AND start_execution_date is not null

AND stop_execution_date is null

FOR XML RAW('tr'), ELEMENTS);

IF @TableBody IS NULL

BEGIN

PRINT 'No Jobs running right now ';

RETURN;

END

ELSE

BEGIN

--DECLARE @AlertMessage VARCHAR(4000);

--DECLARE @RecipientsList VARCHAR(4000);

DECLARE @subject VARCHAR(4000);

-- Replace the entity codes and row numbers

SET @TableBody = Replace(@TableBody, '_x0020_', space(1))

SET @TableBody = Replace(@TableBody, '_x003D_', '=')

SET @TableBody = Replace(@TableBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');

SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail

SET @subject='Currently Running Jobs on '+' '+ @@SERVERNAME

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'default'

,@recipients= 'abc@xyz.com'

,@body = @Document--@alertmessgae

,@body_format = 'HTML'

,@importance = 'Low'

,@subject = @subject

--SET @AlertMessage = 'Currently Running Jobs on '+' '+ @@SERVERNAME + ' '+ 'please check any long running jobs and also we should not have Rebuild Indexes,Rebuild Stats,or Full Backups running at this moment'

--SET @subject='Currently Running Jobs on '+' '+ @@SERVERNAME

END

Tuesday, August 3, 2021

send email everyday with daily full backups take time information

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#LastBackUp') IS NOT NULL DROP TABLE #LastBackUp;

IF OBJECT_ID('tempdb..#CSREPORT') IS NOT NULL DROP TABLE #CSREPORT;

SELECT

bs.database_name,

bs.compressed_backup_size,

bs.backup_start_date,

bs.BACKUP_FINISH_DATE,

bmf.physical_device_name,

--Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name,bs.[type] ORDER BY bs.backup_start_date DESC ),

--d.recovery_model_desc

bs.[type]

into #LastBackUp

FROM msdb.dbo.backupmediafamily bmf

JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id

JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

INNER JOIN master.SYS.databases d on d.name=bs.database_name

WHERE d.state_desc = 'ONLINE'

AND d.is_read_only = 0

AND d.source_database_id IS NULL

and ISNULL(bs.backup_finish_date, GETDATE()-1) > GETDATE()-1

AND bs.type='D'

ORDER BY bms.name

SELECT

@@servername as servername,

CASE WHEN ISNULL((backup_finish_date), GETDATE()-10000) < GETDATE()-31

AND [type] = 'D' THEN 'FAILED'

WHEN ISNULL((backup_finish_date), GETDATE()-10000) < GETDATE()-1

AND [type] = 'I' THEN 'FAILED'

WHEN ISNULL((backup_finish_date), GETDATE()-10000) < GETDATE()-1

AND [type] = 'L' THEN 'FAILED'

WHEN [type] IS NULL THEN 'FAILED'

ELSE 'BACKUP SUCCESS' END AS BackupStatus,

datediff(d, (backup_finish_date), getdate()) as 'full_dayssincelast',

CASE WHEN [type] = 'D' THEN 'Full Backup'

WHEN [type] = 'I' THEN 'Differential Backup'

WHEN [type] = 'L' THEN 'Transaction Log Backup' END AS BackupType,

database_name as databasename ,

backup_start_date,

BACKUP_FINISH_DATE,

CAST((compressed_backup_size) / 1048576 AS DECIMAL(10, 2) ) AS backup_size_mb,

physical_device_name as path,

GETDATE() as insert_date

INTO #CSREPORT FROM #LastBackUp

ORDER BY backup_finish_date desc

DECLARE @tableHTML NVARCHAR(MAX) ;

declare @servername varchar(max)

set @servername=@@SERVERNAME

SET @tableHTML =

N'<H2>Database Backup Status </H2>' +

N'<table border="1" Cellspacing="0" cellpadding="0" style="font-size:x-small;">' +

N'<tr style="background-color:#999999;color:black;font-weight:bold;" align="center">

<td>ServerName</td>' +

N'<td>BackupStatus</td>' +

N'<td> BackupType</td>' +

N'<td> Databasename</td>' +

N'<td> Backup_start_date</td>' +

N'<td> BACKUP_FINISH_DATE</td>' +

N'<td>Backup_size_mb</td>' +

N'<td> Path</td></tr>' +

CAST ( ( SELECT

td = ServerName,'',

td = BackupStatus,'',

td = BackupType,'',

td = Databasename,'',

td = Backup_start_date,'',

td = BACKUP_FINISH_DATE,'',

td = Backup_size_mb,'',

td = Path,''

FROM #CSREPORT

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) )

if (select count(*) FROM #CSREPORT) > 0

Begin

declare @sqlstr varchar (500)

set @sqlstr = 'Database Backup Information '+ @servername

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default', -- DB Mail Profile

@recipients='naresh.koudagani@frontpointsecurity.com', -- Recipients Email

@subject = @sqlstr,

@body = @tableHTML,

@body_format = 'HTML' ;

End

send email daily 8am with disk space available

DECLARE @Document NVARCHAR(MAX)

, @DocumentHead NVARCHAR(MAX)

, @DocumentTail NVARCHAR(MAX)

, @TableHead NVARCHAR(MAX)

, @TableBody NVARCHAR(MAX)

, @TableTail NVARCHAR(MAX)

SET @DocumentHead = N'<html><head>'

+ N'<style>'

+ N'th, td'

+ N'{ '

+ N' border: solid black 1px;'

+ N' padding: 1px 5px 1px 5px;'

+ N' font-size: 11pt;'

+ N'}'

+ N'</style>'

+ N'</head>'

+ N'<body>'

+ N'<h3>'

+ N'Disk Space Available on SQL1 SQL Server'

+ N'</h3>';

SET @DocumentTail = '</body></html>';

SET @TableHead = N'<table cellpadding=0 cellspacing=0 border=0>'

+ N'<tr bgcolor=#C0C0C0>'

+ N' <th>Disk Name</th>'

+ N' <th>Available Space in GB </th>'

+ N'</tr>';

SET @TableTail = '</table>';

CREATE TABLE #tbldiskSpace

(

driveName VARCHAR(1000),

freeSpace FLOAT

)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives

SELECT @TableBody = (SELECT ROW_NUMBER() Over(Order By T1.driveName) % 2 As [TRRow]

,T1.driveName AS [TD],

CONVERT(VARCHAR(MAX), Round((freespace)/1024, 1))

FROM #tbldiskSpace T1 ORDER BY drivename

FOR XML RAW('tr'), ELEMENTS);

IF @TableBody IS NULL

BEGIN

PRINT 'N/A';

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, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')

SET @TableBody = Replace(@TableBody, '<TRRow>0</TRRow>', '');

SELECT @Document = @DocumentHead + @TableHead + @TableBody + @TableTail + @DocumentTail

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default'

, @recipients= 'naresh.koudagani@ABC.com'

, @subject = 'Disk Space Available on SQL SERVER'

, @body = @Document

, @body_format = 'HTML';

END

DROP TABLE #tbldiskSpace

Thursday, July 29, 2021

send space alert

USE [master]

/****** Object: StoredProcedure [dbo].[SendSpaceAlert] Script Date: 7/29/2021 12:36:59 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[SendSpaceAlert]

BEGIN

SET NOCOUNT ON

Declare @Drives Varchar(20)

DECLARE @Spaces Varchar(50)

DECLARE @availableSpace FLOAT

DECLARE @alertMessage VARCHAR(4000)

DECLARE @RecipientsList VARCHAR(4000);

CREATE TABLE #tbldiskSpace

(

driveName VARCHAR(3),

freeSpace FLOAT

)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives

SELECT @Drives = Isnull(@Drives, '') + drivename + ','

FROM #tbldiskspace

WHERE freespace < 20000

SELECT @Spaces = Isnull(@Spaces, '')

+ CONVERT(VARCHAR(10), Round((freespace)/1024, 1))

+ ' GB, '

FROM #tbldiskspace

WHERE freespace < 20000

ORDER BY drivename

IF LEN(@Drives) > 1

BEGIN

SET @alertMessage = 'Disk space is critical on ' + @@SERVERNAME

+ '. Free Space Available on ' + @Drives

+ 'Drive/s is ' + CAST(@Spaces AS VARCHAR)

SET @RecipientsList =N'abc@abccom'

EXEC msdb.dbo.Sp_send_dbmail

@profile_name = 'Default',

@recipients =@RecipientsList,

@body = @alertMessage,

@importance = 'High',

@subject = 'LOW SPACE ON SERVER';

END

DROP TABLE #tbldiskSpace

END

check database status and send email

USE [master]

/****** Object: StoredProcedure [dbo].[Database_Status] Script Date: 7/29/2021 12:26:36 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[Database_Status]

BEGIN

--send email when database changes status--run every 1 min as a job

DECLARE @state VARCHAR(10)

DECLARE @Database_Name VARCHAR(100)

DECLARE @email VARCHAR(100)

SET @state = ''

SET @email = ''

DECLARE db_cursor CURSOR FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('pportal')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @Database_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @state =

CASE

WHEN a.state = 0 THEN 'Online'

WHEN a.state = 1 THEN 'Restoring'

WHEN a.state = 2 THEN 'Recovery_Pending'

WHEN a.state = 4 THEN 'Suspect'

WHEN a.state = 5 THEN 'Emergency'

WHEN a.state = 6 THEN 'Offline'

WHEN a.state = 7 THEN 'Copying - SQL Azure'

WHEN a.state = 10 THEN 'Ofline_Secondary - SQL Azure'

END

FROM sys.databases a WHERE name = @Database_Name

IF @state <> 'Online'

BEGIN

SET @email = 'The database ' + @Database_Name + ' is currently : ' + @state

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

@recipients = 'kumardba99@gmail.com',

@body = @email,

@subject = 'Database Status' ;

END

FETCH NEXT FROM db_cursor INTO @Database_Name

END

CLOSE db_cursor

DEALLOCATE db_cursor

END

Monday, June 14, 2021

Find resource usage by Application

SELECT

CPU = SUM(cpu_time)

,WaitTime = SUM(total_scheduled_time)

,ElapsedTime = SUM(total_elapsed_time)

,Reads = SUM(num_reads)

,Writes = SUM(num_writes)

,Connections = COUNT(1)

,Program = program_name

,LoginName = ses.login_name

FROM sys.dm_exec_connections con

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = con.session_id

GROUP BY program_name, ses.login_name

ORDER BY cpu DESC

What transaction is causing log space to fill out

-- -- -- -- --Transaction causing log space filled most-- -- -- -- --

SELECT tst.[session_id],

s.[login_name] AS [Login Name],

DB_NAME (tdt.database_id) AS [Database],

tdt.[database_transaction_begin_time] AS [Begin Time],

tdt.[database_transaction_log_record_count] AS [Log Records],

tdt.[database_transaction_log_bytes_used] AS [Log Bytes Used],

tdt.[database_transaction_log_bytes_reserved] AS [Log Bytes Rsvd],

SUBSTRING(st.text, (r.statement_start_offset/2)+1,

((CASE r.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE r.statement_end_offset

END - r.statement_start_offset)/2) + 1) AS statement_text,

st.[text] AS [Last T-SQL Text],

qp.[query_plan] AS [Last Plan]

FROM sys.dm_tran_database_transactions tdt

JOIN sys.dm_tran_session_transactions tst

ON tst.[transaction_id] = tdt.[transaction_id]

JOIN sys.[dm_exec_sessions] s

ON s.[session_id] = tst.[session_id]

JOIN sys.dm_exec_connections c

ON c.[session_id] = tst.[session_id]

LEFT OUTER JOIN sys.dm_exec_requests r

ON r.[session_id] = tst.[session_id]

CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st

OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp

where DB_NAME (tdt.database_id) = 'reportingdb'

ORDER BY [Log Bytes Used] DESC;

Find who are in what groups

xp_logininfo 'domaun\Groupname', 'members'--find what logins inside group

purge or delete files older than x days

DECLARE @DeleteDate datetime

SET @DeleteDate = DateAdd(day, -3, GetDate())

EXECUTE master.sys.xp_delete_file 0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

N'\\xyz\abc\12\', -- folder path (trailing slash)

N'bak', -- file extension which needs to be deleted (no dot)

@DeleteDate, -- date prior which to delete

1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

PowerShell script to move files from source location to destination and then delete the file, folder and all files with name

$ErrorActionPreference = "SilentlyContinue"
$path = "\\abc\c\NareshTest.zip"
$dest = "\\xyz\d\abc"

$ErrorActionPreference = "SilentlyContinue"
Expand-Archive -LiteralPath " \\abc\c\NareshTest.zip " -DestinationPath " \\xyz\d\abc" -Force

Copy-Item -Path " \\abc\c\NareshTest .csv" -Destination " \\xyz\d\abc" -Force

Start-Sleep -s 30

Remove-Item -Path " \\abc\c\NareshTest.csv " -Force
Remove-Item -Path " \\abc\c\NareshTest.zip" -Force
<#
Remove-Item -Path " \\abc\c\NareshTest.*" -Force
#>

Thursday, January 28, 2021

delivering replicated transactions no update on subscriber--Replication seems stuck( SOLVED)

delivering replicated transactions

1. I have a translation replication and it runs fine all day, all of a sudden the distribution job says

delivering replicated transactions

2, at 8am when the business starts customer agent /accounting team say data is not updated on the application. ( data gets from another server)

This happens when data is not being updated on the subscriber.

1. I looked at the distribution job which was running for almost 12 hours and no data us being updated and it is holding X lock on Subscriber, not blocking anything or no performance issues except data is not being updated.

I see there are so many indexes on Subscriber matching publisher and thought that could be the reason the update is taking ever

Note: you will have to only keep the indexes u need, find unused and clean them frequently

so i decided to stop the distribution job, drop indexes and start the distribution job -did not work.

tried MSDN, google a lot of suggestions.

btw- make sure replication cleanup jobs run off-hours and they could be sometimes blocking a while cleaning replicates tables ..these also did not help for me.

after 2 days of struggle, i found that daily at 6am there are BULK GP transactions are being posted on the publisher and that is taking ever to get replicated, so I decided to start a snapshot agent( reinitialize subscription or invalidate the snapshot ) at 7am after GP posting is done and then all looks green..

--1.daily 07:15am;

---re intiallize subscription by invaliding the snapshot:job

use DBNAME

exec sp_reinitsubscription

@publication = PUBLICATIONANME',

@subscriber = 'SUBSCRIBER'---all,

,@destination_db ='DBNAME',

@invalidate_snapshot =1

---O/P: Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot.

--RUn below to START SNAPSHOT JOB

USE msdb ;

EXEC dbo.sp_start_job N'SNAPSHOT AGENT JOB' ;

--MONITOR SNAPSHOT AND DISTRIBUTION JOB: BOTH SHOULD RUN, WAIT FOR 5 MINUTES IF NOT START DISTRIBUTION JOB:

---IF worked PLAN TO AUTOMATE ..until the tables data moves to history

check snapshot agent repl data folder

also, check the count of rows using except

IF NOT EXISTS (

select count(*) from DBname.dbo.Table with (nolock)---20574581

EXCEPT

select count(*) from Subscriber server. dbname..dbo.table with (nolock)---20574581

)

if matches or no send an email ...

immediatesnapshot=1, allowanonymus=1: don't change :::

also, check indexes on the subscriber( in my case 1 was only useful)

Cannot construct data type date, some of the arguments have values which are not valid.

drop table test

create table test

(ExpDate varchar(20))

insert into test values

('06/22'), ('01/22'), ('02/22'), ('02/20'), ('01/20')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

ExpDate

----------

2020-02-01

2020-01-01

insert into test values ('13/22')

;with cte as (

Select DATEFROMPARTS(2000 + CAST(right(ExpDate,2) AS INT), CAST(left(ExpDate,2) AS INT), 1) AS ExpDate

from test

)

SELECT ExpDate

from cte

WHERE(ExpDate) BETWEEN DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) AND DATEADD(MM,DATEDIFF(MM,0,GETDATE())+1,0)

ExpDate

----------

2020-02-01

2020-01-01

Msg 289, Level 16, State 1, Line 22

Cannot construct data type date, some of the arguments have values which are not valid.

--using CTE tables and DATEFROMPARTS: did not work

--Cannot construct data type date, some of the arguments have values which are not valid.

-----USE TEMP TABLES INSTED OF CTE ,