SQL Job Hung

Check if there are any other jobs running at the same time, especially FULL backup and Reindex jobs (I'm more for this one) since they can cause locks. If so, then you need to change the schedule of your job or the other job that runs at the same time.

I have seen it already. There is a another job name syspolicy purge history which run at 02.00.01 am. in the attachment failed job i just killed earlier since it was long running basically it call proc on a db. But it success every day except Sunday Night. Please see the attachment.

Nothing attached.
Mind that usually jobs at Sunday runs longer because DBAs tend to move all heavy jobs to run on weekends.
Also, don't look only for jobs that starts around 02:00AM. Also look for the ones that started before but takes longer to finish. It might happen that at 02:00AM they still running and when your wants to run it's already stacked by the other one.

Got it. I am checking. Thanks!

Here is the attachment.
Capture.PNG

The screenshot doesn't give the information of the jobs duration.
I can also see that you're using the standard SQL Server maintenance jobs. They are ok for backups but I would avoid to use them to perform any other kind of maintenance tasks. I recommend you to give a try on Ola Hallengren's scripts. They are very known and wide used by DBAs around the world.

What does the "Whatsup - Table maintenance" job do?

Whatsup is one of our db. The table maintenance job clean up older data. I already checked all other jobs are not overlapping my problematic job's run duration. I guess i need to check on Saturday night if anything blocking on that job. Thanks for your suggestions about  Ola Hallengren's Scripts. Could you please tell me what will be the difference if i set up Ola Hallengren Script instead of SQL Maintenance Plan Jobs.

Thanks!

Could you please tell me what will be the difference if i set up Ola Hallengren Script instead of SQL Maintenance Plan Jobs.

For indexes, it only reindex the necessary ones and has enough intelligence to know when to rebuild and when to just perform a reorg. SQL Server default reindex maintenance task always perform rebuild for all existing indexes. You can save hours and disk space on very large databases if you use Ola's solution.

Hi Vitor,

Thanks for your explanation. Basically my that sql job run after one of my SSIS csv import job run. Could you please help me out there is an one step failed but i checked the connection is ok.

[Execute SQL Task] Error: Executing the query "
declare @toparse nvarchar(max)
declare @strleng..." failed with the following error: "Invalid length parameter passed to the LEFT or SUBSTRING function.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.

Where this error is coming from?

Vitor,

I have a SQL Job this job basically execute SSIS Package. It does import csv files. In there is a step execute T-SQL statement Task.

The following query execution failed.Ii had a similar query for the other step but that one success without any error if i run in SSMS.


declare @toparse nvarchar(max)
declare @strlength int
declare @separator_location int
declare @instance nchar(10)
declare @instance_url nvarchar(100)

declare @hostname nvarchar(50)
declare @strIn nvarchar(2000)
declare @separator nchar(2) = ' '
declare @inner_separator nchar(1) = '|'

DECLARE @item nvarchar(1000)  -- assume maximun item length 1000 char
DECLARE @len int, @sepPos1 int,  @sepPos2 int

-- truncate table staging_oracle_version
--declare @truncate varchar(200)
--set @truncate = 'truncate table staging_oracle_version'
--exec (@truncate)

-- get oracle instances to load
declare  oracleinstance_csr cursor for
select certname
      , value
from puppetdb_certname_facts
where fact like 'oracle_instance_url'
   and value IS NOT NULL
   and value !=''
  and value != '""'

open oracleinstance_csr

fetch next from oracleinstance_csr into @hostname, @strIn

--select @hostname, @strIn

while @@FETCH_STATUS = 0
begin

  SET @strIn = REPLACE(@strIn, char(10), @separator)  -- Replace all the LF (char(10)) from the string and replace with defined separator [ the code for CR is char(13)]
  Set @sepPos1 = 1
  set @sepPos2 = charindex(@separator, @strIn)

  SET @strin = @strIn + @separator

  IF @sepPos2 = 0  -- nothing to parse here
  BEGIN
     SET @item = @strIn
     SET @toparse =  ( RTRIM(LTRIM(@item)))
     
       select @strlength = LEN(@toparse)
       select @separator_location = charindex(@inner_separator, @toparse)

      
       select @instance = LEFT(@toparse,(@separator_location-1))




       select @instance_url = RIGHT(@toparse, (@strlength-@separator_location))

        update oracleinstances
        set instance_url  = @instance_url
        where instanceName = @instance
          and hostname = @hostname
       -- select @instance, @hostname, @version
      
  END
ELSE
  BEGIN
    WHILE @sepPos2 > 0
    BEGIN
        -- get parse through the values
      SET @item = substring(@strIn, @sepPos1,@sepPos2-1)
      SET @toparse =  ( RTRIM(LTRIM(@item)))
     
        select @strlength = LEN(@toparse)
        select @separator_location = charindex(@inner_separator, @toparse)

        select @instance = LEFT(@toparse,(@separator_location-1))
        select @instance_url = RIGHT(@toparse, (@strlength-@separator_location))

        
        update oracleinstances
        set instance_url = @instance_url
        where instanceName = @instance
          and hostname = @hostname

      SET @sepPos1 = @sepPos1 + @sepPos2
      set @sepPos2 = charindex(@separator, substring(@strIn, @sepPos1,100))
    END
 

  END

  fetch next from oracleinstance_csr into @hostname, @strIn
           
END

close oracleinstance_csr
deallocate oracleinstance_csr


Msg 537, Level 16, State 3, Line 54
Invalid length parameter passed to the LEFT or SUBSTRING function.

Line 54 is this one:

select @instance = LEFT(@toparse,(@separator_location-1))

Select allOpen in new window

Going a line back I can see that you're trying to find the position of the inner separator:

select @separator_location = charindex(@inner_separator, @toparse)

Select allOpen in new window

You never validate if the inner separation is found or not so you're assuming that will always find it but looks like this time it didn't and that's why you're getting the error.

Thanks. I have the other step for version. Here the following query working fine.

select @separator_location = charindex(@inner_separator, @toparse)

select @instance = LEFT(@toparse,(@separator_location-1))
select @version = RIGHT(@toparse, (@strlength-@separator_location))

So if i inner separator found then should i comment out these lines. I checked it works fine when i comment out.

 --select @separator_location = charindex(@inner_separator, @toparse)
 --select @instance = LEFT(@toparse,(@separator_location-1))

But if you commenting those lines then the next update won't update nothing since @instance is empty:

Is that what you want?

Actually i need the data this job generates reports. I am still not clear how it works fine for the following steps.  Our source file is csv where all data dump into a table:--


declare @toparse nvarchar(max)
declare @strlength int
declare @separator_location int
declare @instance nchar(10)
declare @version nchar(15)
declare @hostname nvarchar(50)
declare @strIn nvarchar(2000)
declare @separator nchar(2) = ' '
declare @inner_separator nchar(1) = '|'

DECLARE @item nvarchar(1000)  -- assume maximun item length 1000 char
DECLARE @len int, @sepPos1 int,  @sepPos2 int

-- truncate table staging_oracle_version
--declare @truncate varchar(200)
--set @truncate = 'truncate table staging_oracle_version'
--exec (@truncate)

-- get oracle instances to load
declare  oracleinstance_csr cursor for
select certname
      , value
from puppetdb_certname_facts
where fact like 'oracle_version'
   and value IS NOT NULL
   and value !=''
 and value != '""'

open oracleinstance_csr

fetch next from oracleinstance_csr into @hostname, @strIn

--select @hostname, @strIn

while @@FETCH_STATUS = 0
begin

  SET @strIn = REPLACE(@strIn, char(10), @separator)  -- Replace all the LF (char(10)) from the string and replace with defined separator [ the code for CR is char(13)]
  Set @sepPos1 = 1
  set @sepPos2 = charindex(@separator, @strIn)

  SET @strin = @strIn + @separator

  IF @sepPos2 = 0  -- nothing to parse here
  BEGIN
     SET @item = @strIn
     SET @toparse =  ( RTRIM(LTRIM(@item)))
     
       select @strlength = LEN(@toparse)
       select @separator_location = charindex(@inner_separator, @toparse)

      
       select @instance = LEFT(@toparse,(@separator_location-1))
       select @version = RIGHT(@toparse, (@strlength-@separator_location))

        update oracleinstances
        set [version] = @version
        where instanceName = @instance
          and hostname = @hostname
       -- select @instance, @hostname, @version
      
  END
ELSE
  BEGIN
    WHILE @sepPos2 > 0
    BEGIN
        -- get parse through the values
      SET @item = substring(@strIn, @sepPos1,@sepPos2-1)
      SET @toparse =  ( RTRIM(LTRIM(@item)))
     
        select @strlength = LEN(@toparse)
        select @separator_location = charindex(@inner_separator, @toparse)

        select @instance = LEFT(@toparse,(@separator_location-1))
        select @version = RIGHT(@toparse, (@strlength-@separator_location))

        
        update oracleinstances
        set [version] = @version
        where instanceName = @instance
          and hostname = @hostname

      SET @sepPos1 = @sepPos1 + @sepPos2
      set @sepPos2 = charindex(@separator, substring(@strIn, @sepPos1,100))
    END
 

  END

  fetch next from oracleinstance_csr into @hostname, @strIn
           
END

close oracleinstance_csr
deallocate oracleinstance_csr


Please help how can i fix this for the first query.

Now it is working fine. Before i ending up i am explaining there are a couple execute sql task which is a SSIS package that basically import data from csv file. This csv file are coming from oracle puppet db. I run a report from a table.

This step need to gather information about oracle instance  url for reporting purposes.

I am going to edit this step in my SSIS package. Can i good to go.

Thanks!

It is working fine. Thank you so much for your help.