Tagged: Jobs

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

The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context

My Development team contacted me and said they are not able to access database  “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context”

when I looked into server server I found –

  1. Login was there and access was granted.
  2. When I looked into database I found user was disable ( Red down arrow mark )

2

Here are two solutions –

  1. Most of the people recommended to recreate use by deleting and reading into database.

But main reason why it’s disabled because user is not having connect permission to database.

  1. if you grant connect permission it will resolve your issue.

use [YourDBName]

go

grant connect to [xyz\user]

1

How Get SQL Server Job Name with help of program name

How Get SQL Server Job Name with help of program name

SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4)

Step -1 Create Below function

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[GetJobIdFromProgramName] (

@program_name nvarchar(128)

)

RETURNS uniqueidentifier

AS

BEGIN

DECLARE @start_of_job_id int

SET @start_of_job_id = CHARINDEX(‘(Job 0x’, @program_name) + 7

RETURN CASE WHEN @start_of_job_id > 0 THEN CAST(

SUBSTRING(@program_name, @start_of_job_id + 06, 2) + SUBSTRING(@program_name, @start_of_job_id + 04, 2) +

SUBSTRING(@program_name, @start_of_job_id + 02, 2) + SUBSTRING(@program_name, @start_of_job_id + 00, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 10, 2) + SUBSTRING(@program_name, @start_of_job_id + 08, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 14, 2) + SUBSTRING(@program_name, @start_of_job_id + 12, 2) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 16, 4) + ‘-‘ +

SUBSTRING(@program_name, @start_of_job_id + 20,12) AS uniqueidentifier)

ELSE NULL

END

END

Step – 2

Execute below query –

SELECT *

FROM msdb.dbo.sysjobs

WHERE

job_id = dbo.GetJobIdFromProgramName (‘SQLAgent – TSQL JobStep (Job 0x0854F6E4F9E5AE48B164966C0553C8B0 : Step 4) ‘)