Friday, October 28, 2022

patrickashoursqlblogger good evening!

Date: Sat, 29 Oct 2022 05:13:02 +0300
Message-Id: <360806vHZqwV$AHV6UHMC$QDwwkxcb$@studio75.com.mx>
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_000_0077_LOSLROHO.8LFCKYLO"
X-Mailer: Microsoft Outlook 16.0
Thread-Index: AdjrPAwKUbC3EQNNEM2KIfyOV1ahXA==
Content-Language: en-us

This is a multipart message in MIME format.

------=_NextPart_000_0077_LOSLROHO.8LFCKYLO
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

patrickashoursqlblogger https://search.yahoo.com/search?p=patrick_ashour.sqlblogger@blogger.com Patrick

------=_NextPart_000_0077_LOSLROHO.8LFCKYLO
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV=3D"Content-Type" CONTENT=
=3D"text/html; charset=3Dus-ascii"><meta name=3DGenerator content=3D"Micros=
oft Word 15 (filtered medium)"><style></style></head><body link=3D"#0563C1" vlink=3D"#9=
54F72"><div class=3DWordSection1>patrickashoursqlblogger<o:p></o:p></p=
><=
o:p> </o:p><o:p> </o:p><=
span style=3D'font-size:10.2pt;font-family:Tahoma'><a href=3D"https://bit.l=
y/3DdkhL3">https://search.yahoo.com/search?p=3Dpatrick_ashour.sqlblogger@bl=
ogger.com</a><o:p></o:p><o:p> </o:p><o:p> </o:p=
>Patrick<o:p></o:p></div></body></html>
------=_NextPart_000_0077_LOSLROHO.8LFCKYLO--

Thursday, June 16, 2022

Good afternoon, patrick ashour sqlblogger

Patrick ashour sqlblogger

https://www.google.com/search?q=patrick_ashour.sqlblogger@blogger.com

Patrick
Patrick

Thursday, July 18, 2019

http://atharvamane.com/indication.php?thau=SIYJT1400

Wednesday, October 05, 2016

Hi

hi

http://aicb.draftfcbstaging.com/roll.php?pass=19b5qq7uzky0

Thanks!

Patrick

Friday, January 29, 2016

hello

Hi

http://estateguys.com/saved.php?chance=rrcvq119m41z

Patrick

Tuesday, October 14, 2014

SQL - find problems - Standard solutions to random problems

Specialized Performance Troubleshooting (Part 1: How to troubleshoot Forwarded Records)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/04/specialized-performance-troubleshooting-part-1-how-to-troubleshoot-forwarded-records.aspx

Specialized Performance Troubleshooting (Part 2: How to troubleshoot Memory problems in SQL Server)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/11/specialized-performance-troubleshooting-part-2-how-to-troubleshoot-memory-problems-in-sql-server.aspx

Specialized Performance Troubleshooting (Part 3: How to identify storage issues at a SQL Server box)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/11/18/specialized-performance-troubleshooting-part-3-how-to-identify-storage-issues-at-a-sql-server-box.aspx

How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to collect a detailed Perfmon trace)

http://blogs.msdn.com/b/john_daskalakis/archive/2013/10/07/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-1-how-to-collect-a-detailed-perfmon-trace.aspx

http://blogs.msdn.com/b/john_daskalakis/

Monday, September 08, 2014

proc sp_alert_failed_jobs

/*
exec sp_alert_failed_jobs @pastdays = -3 , @exludejob ='''exportbill_dts'',''daily_update_erptables'''
*/
alter proc sp_alert_failed_jobs
(
    @pastdays int = -1,
    @exludejob varchar(1000) =''
)

as
begin

--declare @pastdays int = -1
--select @exludejob
if(object_id('msdb..__tmp_failed_jobs')>1)
    drop table __tmp_failed_jobs
create table __tmp_failed_jobs (name varchar(1000), run_date int, run_time int, step_id int, step_name varchar(1000), message ntext)

declare @sql nvarchar(max)

set @sql = 'select j.name,h.run_date,h.run_time,h.step_id, h.step_name, h.message
From msdb..sysjobhistory h
inner join msdb..sysjobs j on h.job_id = j.job_id
where h.run_status = 0
and h.run_date >= convert(int, CONVERT(varchar, dateadd(dd,'+convert(varchar,@pastdays)+',GETDATE()),112))
and j.name not in ('+@exludejob+')
order by h.run_date desc, h.step_id asc'

--print @sql
insert into __tmp_failed_jobs exec sp_executesql @sql

--select * From __tmp_failed_jobs

if(select COUNT(*) from __tmp_failed_jobs) >0
begin
    declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
    set @subject = 'Failed Jobs - SQL ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
    set @recipientslist = 'patrickalexander@hotmail.com;ACheckDB@acheckamerica.com;kcinty@acheckamerica.com;qkong@acheckamerica.com'

    DECLARE @tableHTML NVARCHAR(MAX) , @subjectstring nvarchar(200);

    SET @tableHTML =
        N'

Failed jobs on '+@@SERVERNAME+'

' +
 N'' +
 N' ' +
 N'' +
 N'' +
 N'< ' +
 CAST ( ( SELECT td = name, '',
 td = convert(varchar,run_date), '',
 td = convert(varchar,run_time), '',
 td = step_id, '',
 td = step_name, '',
 td = message
 FROM __tmp_failed_jobs
 FOR XML PATH('tr'), TYPE
 ) AS NVARCHAR(MAX) ) +
 N'

Job Name	Date	Time	StepID	StepName	Message

' ;

        --print @tableHTML

        EXEC msdb.dbo.sp_send_dbmail
            --@profile_name = 'default',
            @recipients =@recipientslist,
            @body = @tableHTML,
            @body_format = 'HTML' ,
            @subject = @subject;
end

end

Tuesday, November 26, 2013

Job History Collection

--TABLE
USE [DBACollection]
GO

/****** Object: Table [dbo].[JobHistoryCollection] Script Date: 11/26/2013 10:30:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[JobHistoryCollection](
 [ServerName] [nvarchar](128) NULL,
 [JobName] [sysname] NULL,
 [Enabled] [tinyint] NULL,
 [CurrentStatus] [varchar](9) NULL,
 [CurrentStepNbr] [int] NULL,
 [LastRunTime] [datetime] NULL,
 [LastRunOutcome] [varchar](11) NULL,
 [LastRunDuration] [int] NULL,
 [CollectionTime] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[JobHistoryCollection] ADD CONSTRAINT [DF_JobHistoryCollection_CollectionTime] DEFAULT (getdate()) FOR [CollectionTime]
GO

--PROC
USE [DBACollection]
GO
/*

exec pr_JobHistoryCollection

select * from JobHistoryCollection order by collectiontime desc

select jobname, COUNT(*) as cc, MAX(lastrunduration) mx,Min(lastrunduration) mn,Avg(lastrunduration) av
from JobHistoryCollection
where collectiontime < GETDATE() and collectiontime > DATEADD(dd, -7, GETDATE())
group by jobname
order by mx desc

*/
alter proc pr_JobHistoryCollection
as
begin

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

if(object_id('tempdb..#tmp')>1)
 drop table #tmp

create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);

insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLA4SW1';

UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);

--select * from #tmp

insert into JobHistoryCollection
(ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration)

select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from
(
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
h.run_duration
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where x.running != 1
) a
except
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from JobHistoryCollection

end
--JOB
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'AUDIT - JobHistoryCollection',
 @enabled=1,
 @notify_level_eventlog=0,
 @notify_level_email=2,
 @notify_level_netsend=2,
 @notify_level_page=2,
 @delete_level=0,
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AUDIT - JobHistoryCollection', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AUDIT - JobHistoryCollection', @step_name=N'exec sp',
 @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_JobHistoryCollection',
 @database_name=N'DBACollection',
 @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AUDIT - JobHistoryCollection',
 @enabled=1,
 @start_step_id=1,
 @notify_level_eventlog=0,
 @notify_level_email=2,
 @notify_level_netsend=2,
 @notify_level_page=2,
 @delete_level=0,
 @description=N'',
 @category_name=N'[Uncategorized (Local)]',
 @owner_login_name=N'sa',
 @notify_email_operator_name=N'',
 @notify_netsend_operator_name=N'',
 @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'AUDIT - JobHistoryCollection', @name=N'Every 15 min',
 @enabled=1,
 @freq_type=4,
 @freq_interval=1,
 @freq_subday_type=4,
 @freq_subday_interval=15,
 @freq_relative_interval=0,
 @freq_recurrence_factor=1,
 @active_start_date=20131126,
 @active_end_date=99991231,
 @active_start_time=0,
 @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

Wednesday, September 25, 2013

Retrieve job status

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_job_status]    Script Date: 09/25/2013 11:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec [sp_job_status] --EVERYTHING
exec [sp_job_status] @jobstatus = 0 --FAIL
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector'
exec [sp_job_status] @jobname = 'CSOD Service Queue Collector' , @jobstatus = 1 --Success
*/
ALTER procedure [dbo].[sp_job_status]
(
    @isrunning bit = 0 ,
    @jobname nvarchar(255) = null ,
    @jobstatus int = null
)
as
begin

/*
@jobstatus
WHEN NULL THEN 'ALL'
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
*/

    --set @jobname = 'CSOD Service Queue Collector'
    --set @isrunning = 0 -- 0:all 1:running

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE   id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]'))
        DROP TABLE [tempdb].[dbo].[Temp1]

    CREATE TABLE [tempdb].[dbo].[Temp1]
    (
    job_id uniqueidentifier NOT NULL,
    last_run_date nvarchar (20) NOT NULL,
    last_run_time nvarchar (20) NOT NULL,
    next_run_date nvarchar (20) NOT NULL,
    next_run_time nvarchar (20) NOT NULL,
    next_run_schedule_id INT NOT NULL,
    requested_to_run INT NOT NULL,
    request_source INT NOT NULL,
    request_source_id sysname
    COLLATE database_default NULL,
    running INT NOT NULL,
    current_step INT NOT NULL,
    current_retry_attempt INT NOT NULL,
    job_state INT NOT NULL)

    DECLARE @job_owner   sysname
    DECLARE @is_sysadmin   INT
    SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
    SET @job_owner   = suser_sname ()

    INSERT INTO [tempdb].[dbo].[Temp1]
    --EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
    UPDATE [tempdb].[dbo].[Temp1]
    SET last_run_time    = right ('000000' + last_run_time, 6),
    next_run_time    = right ('000000' + next_run_time, 6);
    -----
    SELECT j.name AS JobName,
    j.enabled AS Enabled,
    CASE x.running
    WHEN 1
    THEN
    'Running'
    ELSE
    CASE h.run_status
    WHEN 2 THEN 'Inactive'
    WHEN 4 THEN 'Inactive'
    ELSE 'Completed'
    END
    END
    AS CurrentStatus,
    coalesce (x.current_step, 0) AS CurrentStepNbr,
    CASE
    WHEN x.last_run_date > 0
    THEN
    convert (datetime,
    substring (x.last_run_date, 1, 4)
    + '-'
    + substring (x.last_run_date, 5, 2)
    + '-'
    + substring (x.last_run_date, 7, 2)
    + ' '
    + substring (x.last_run_time, 1, 2)
    + ':'
    + substring (x.last_run_time, 3, 2)
    + ':'
    + substring (x.last_run_time, 5, 2)
    + '.000',
    121
    )
    ELSE
    NULL
    END
    AS LastRunTime,
    CASE h.run_status
    WHEN 0 THEN 'Fail'
    WHEN 1 THEN 'Success'
    WHEN 2 THEN 'Retry'
    WHEN 3 THEN 'Cancel'
    WHEN 4 THEN 'In progress'
    END
    AS LastRunOutcome,
    CASE
    WHEN h.run_duration > 0
    THEN
    (h.run_duration / 1000000) * (3600 * 24)
    + (h.run_duration / 10000 % 100) * 3600
    + (h.run_duration / 100 % 100) * 60
    + (h.run_duration % 100)
    ELSE
    NULL
    END
    AS LastRunDuration
    FROM          [tempdb].[dbo].[Temp1] x
    LEFT JOIN
    msdb.dbo.sysjobs j
    ON x.job_id = j.job_id
    LEFT OUTER JOIN
    msdb.dbo.syscategories c
    ON j.category_id = c.category_id
    LEFT OUTER JOIN
    msdb.dbo.sysjobhistory h
    ON     x.job_id = h.job_id
    AND x.last_run_date = h.run_date
    AND x.last_run_time = h.run_time
    AND h.step_id = 0
    where (@jobstatus is null or h.run_status = @jobstatus)
    and (@jobname is null or j.name = @jobname )
    --x.running = 1 -- to see running jobs



end

Useful SQL Server scripts