Tagged: SQL 2014

Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.

Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –

 

1

Solutions – On error message it’s look like some other processes was trying to use SQL default port 1433.

Hence it was not allowing to restart SQL Services with Port 1433.

On my server, we have multiple SQL Services installed ( SQL 2008 to SQL vNext ) hence I started checking if any of these SQL Server have assigned static port 1433.

I start checking assigned port for all SQL Server and I found SQL Server 2014 and SQL Server 2016 was having assigned static port 1433.

In order to resolve the issue, I removed static port entry and restart my SQL Services and it started successfully.

2

34

Advertisements

Create Blocking Alert – SQL 2005 , SQL 2008 R2 and SQL 2012

We often need to create blocking alert in our Production server to capture existing blocking on server and keep record for future analysis.

Here are steps which help us to create blocking alerts-

Either you can receive blocking details through e-mail or you can log event id in application log and configure your monitoring tool to capture from application log.

STEP 1 –  Create SP to capture blocking details and generate e-mail\ event id in application log.

Note –  and datediff(minute, last_batch, getdate()) > 15, through which you can define blocking duration to generate e-mail or event, I am using 15 min

USE [db]

go

/****** Object: StoredProcedure [dbo].[usp_checkForLongRunningBlockers_log] Script Date: 5/18/2015 9:22:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[usp_checkForLongRunningBlockers_log]

as

declare @@leadblocker_spid smallint

declare @@dbname nvarchar(128)

declare @@login_time datetime

declare @@last_batch datetime

declare @@open_tran smallint

declare @@status nchar(30)

declare @@hostname nchar(128)

declare @@program_name nchar(128)

declare @@cmd nchar(16)

declare @@loginame nchar(128)

declare @@net_address nchar(12)

declare @@net_library nchar(12)

declare @@string varchar(2000)

select

@@leadblocker_spid = sp.spid,

@@dbname = db_name(sp.dbid),

@@login_time = login_time,

@@last_batch = last_batch,

@@open_tran = open_tran,

@@status = sp.status,

@@hostname = hostname,

@@program_name = program_name,

@@cmd = cmd,

@@loginame = loginame,

@@net_address = net_address,

@@net_library = net_library

FROM master.dbo.sysprocesses sp

LEFT JOIN sys.dm_exec_requests er

ON er.session_id = sp.spid

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

AND blocked = 0

and datediff(minute, last_batch, getdate()) > 15

set @@string =

‘Blocking Details -Date: ‘ + convert(varchar(20), getdate(), 9) + ‘

@@leadblocker_spid = ‘ + convert(varchar(5),@@leadblocker_spid) + ‘

@@dbname = ‘ + @@dbname + ‘

@@login_time = ‘ + convert(varchar(20), @@login_time, 9) + ‘

@@last_batch = ‘ + convert(varchar(20), @@last_batch, 9) + ‘

@@open_tran = ‘ + convert(varchar(5), @@open_tran) + ‘

@@status = ‘ + @@status + ‘

@@hostname = ‘ + @@hostname + ‘

@@program_name = ‘ + @@program_name + ‘

@@cmd = ‘ + @@cmd + ‘

@@loginame = ‘ + @@loginame + ‘

@@net_address = ‘ + @@net_address + ‘

@@net_library = ‘ + @@net_library + ”

if @@leadblocker_spid is null

return

else

–exec dbo.sp_SQLSMTPMail_html

— @vcTo = ‘nshukla@xyz.com’,

— @vcBody = @@string,

— @vcSubject = ‘Long Running Blocking’,

— @vcAttachments = null,

— @vcQuery = null,

— @vcFrom = ‘noreply@xyz.com’,

— @vcCC = ”,

— @vcBCC = ”,

— @vcSMTPServer = ‘nav-mail2.xyz.com’, — put local network smtp server name here

— @cSendUsing = ‘2’,

— @vcPort = ’25’,

— @cAuthenticate = ‘0’,

— @vcDSNOptions = ‘0’,

— @vcTimeout = ’30’,

— @vcSenderName = null,

— @vcServerName = null

EXEC xp_logevent 60000, @@string, error

GO

STEP 2 – Create table to capture blocking history –

USE [db]

GO

/****** Object:  Table [dbo].[BLOCKING_HISTORY]    Script Date: 5/18/2015 9:26:40 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BLOCKING_HISTORY](

[spid] [smallint] NULL,

[kpid] [smallint] NULL,

[blocked] [smallint] NULL,

[waittype] [varbinary](5000) NULL,

[waittime] [bigint] NULL,

[lastwaittype] [nvarchar](500) NULL,

[waitresource] [nvarchar](500) NULL,

[dbid] [smallint] NULL,

[uid] [smallint] NULL,

[cpu] [int] NULL,

[physical_io] [bigint] NULL,

[memusage] [int] NULL,

[login_time] [datetime] NULL,

[last_batch] [datetime] NULL,

[ecid] [smallint] NULL,

[open_tran] [smallint] NULL,

[status] [nvarchar](500) NULL,

[hostname] [nvarchar](500) NULL,

[program_name] [nvarchar](500) NULL,

[hostprocess] [nvarchar](500) NULL,

[cmd] [nvarchar](500) NULL,

[nt_domain] [nvarchar](500) NULL,

[nt_username] [nvarchar](500) NULL,

[net_address] [nvarchar](500) NULL,

[net_library] [nvarchar](500) NULL,

[loginame] [nvarchar](500) NULL,

[context_info] [varbinary](5000) NULL,

[sql_handle] [varbinary](5000) NULL,

[stmt_start] [int] NULL,

[stmt_end] [int] NULL,

[request_id] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

STEP 3 – Create SP to INSERT  blocking details in Blocking History table.

USE [db]

GO

/****** Object:  StoredProcedure [dbo].[usp_blockingEntry]    Script Date: 5/18/2015 9:25:52 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure [dbo].[usp_blockingEntry]

as

begin

SET NOCOUNT ON

declare @blockingcount int  =   ( select count(1) from sys.sysprocesses where blocked 0 )

if @blockingcount > 0

INSERT INTO [dbo].[BLOCKING_HISTORY]

(      spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

)

select

spid

,      kpid

,      blocked

,      waittype

,      waittime

,      lastwaittype

,      waitresource

,      dbid

,      uid

,      cpu

,      physical_io

,      memusage

,      login_time

,      last_batch

,      ecid

,      open_tran

,      status

–,    sid

,      hostname

,      program_name

,      hostprocess

,      cmd

,      nt_domain

,      nt_username

,      net_address

,      net_library

,      loginame

,      context_info

,      sql_handle

,      stmt_start

,      stmt_end

,      request_id

from sys.sysprocesses  where blocked 0

end

GO

STEP – 4 – Create SP to get lead blocker –

USE [db]
GO

/****** Object: StoredProcedure [dbo].[usp_leadblocker] Script Date: 5/18/2015 9:44:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[usp_leadblocker]
as

select *
from
(
select
‘Blocking’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username

from sys.sysprocesses (nolock)
where
spid in (
select distinct blocked
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
)
and blocked = 0
and blocked spid

union all

select
‘Blocked’ as State,
hostname as host,
db_name(dbid) as dbname,
SPID,
blocked,
lastwaittype,
dbid,
login_time,
last_batch,
open_tran,
status,
hostname,
program_name,
cmd,
nt_domain,
nt_username
from sys.sysprocesses (nolock)
where
blocked 0
and blocked spid
) dt
order by 1 desc

GO

STEP 5 – T- SQL to check SPID text.

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle

FROM sys.sysprocesses

WHERE spid = 64

SELECT TEXT

FROM sys.dm_exec_sql_text(@sqltext)

GO

***************************************

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 64
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO

*****************************

DBCC INPUTBUFFER(64)
GO

*************************

SP_WHO2 64

go