SPID OF RUNNING JOBS IN SQL SERVER

Today I was alerted by one of our long running monitor Job saying that one particular job is running for longer duration than its Average running time.


Now I had logged into my SQL server and queried the sysprocesses table with the below command to get the SPID of the job which is taking much time…

Select * from sys.sysprocesses where program_name like ‘%SQLAgent - TSQL JobStep%’

but unfortunately I got more than 10 records which means there are 10 jobs running on the instance and you know that Program_name columns value has Hexadecimal form,So I need to get the SPID of the Job and the Job Name by using an SQL query ..


So if you guys have the same situation then below query will help you in retrieving the SPID and job names …..

QUERY:

SELECT DISTINCT SP.SPID, SJ.NAME FROM MASTER.DBO.SYSPROCESSES SP JOIN MSDB.DBO.SYSJOBS SJ ON

MASTER.DBO.FN_VARBINTOHEXSTR(CONVERT(VARBINARY(16), JOB_ID)) COLLATE LATIN1_GENERAL_CI_AI =

SUBSTRING(REPLACE(PROGRAM_NAME, 'SQLAGENT - TSQL JOBSTEP (JOB ', ''), 1, 34)

Hope this helps…..

Transaction Log shipping Restore Job running very slow in STAND BY MODE-Explanation

Description:


Sometimes you may notice your restore job on the secondary server will be taking much more time than usual, so what is wrong??

This is because of long running transactions (uncommitted transactions) spanning multiple log backups and the LS being configured as STANDBY…

Detailed Explanation:

• When restoring transaction log with the “STAND BY” option, SQL Server will go over the transaction log and save all uncommitted transactions to a file with .tuf(Transaction UNDO File) extension.

• You will not see the uncommitted data in the secondary DB even if you read the data in “READ Uncommitted Isolation Level". Thus is, because the SQL Server performs UNDO for all uncommitted transactions and saves their data to the tuf file. It simply saves the entire content of the pages that it had to undo.



The tuf file will be used when you restore the next transaction log file. First, the SQL Server will REDO all the uncommitted transactions that are in the tuf file and then continue to restore the next transaction log.The tuf file will contain the uncommitted transactions not just from the latest transaction log but uncommitted transactions in all previous transaction logs that have been restored.

• So with the above explanation let us assume a rebuild index on a big table that has run for 45 mins and let’s say 3 log backups were taken in this duration, so first log backup uncommitted transactions will be written to tuf file while restoration and when the second log backup is being restored, the sql server will redo all the uncommitted transactions from the tuf file and will continue the restoring the log backup but the second log backup also has uncommitted transactions so SQL server will again UNDO the index operation and will save the uncommitted transactions to tuf file and this process continues until transaction is committed in further log backups causing the delay in LOG RESTORE…

I hope above explanation had helped you understand the reason behind slow restore on secondary stand by log shipping server.

References: Dan's madebysql Blog.

An attempt was made to send an email when no email session has been established

You may come across a scenario where you will be able to send mails through DB Test mail but the notifications configured on the job schedules will not trigger any mails on job failures/success…So what’s wrong???
As a first step you need to check whether SQL Server agent is configured to use DB MAIL profile or not,below are the steps to check the same….

• Right click on SQL Server Agent Select PropertiesSelect ALERT SYSTEM tab Make sure that ENABLE MAIL PROFILE is checkedSelect mail profile Click on OK.

o So if above is not enabled this is reason of your behavior if in case if you are facing the error in spite of enabling this setting then follow the below steps for resolution.

• These steps need your SQL AGENT to be restarted so please be careful to check on any running jobs and go ahead ….
o Uncheck “Enable Mail profile” on SQL Agent Properties. Stop the Agent and Start the Agent.
o Now Enable database mail in properties and restart the SQL Server Agent.
o This will let you to get rid of error message “An attempt was made to send an email when no email session has been established” and you will be able to successfully get the mail from the agent notification on Job failures/Success

DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

You may come across a scenario where your SQL Agent job scheduled to run a Delete statement against a table fails with the below error.


DELETE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

But the same delete statement runs fine when you run it from SSMS, so what’s wrong on my agent job??? Below is the reason for failure…
From the error message it is quite clear that delete operation has failed on a table which has indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
So for these type of operations we need to Set Quoted_Identifier to ON, whenever a new connection is made through SSMS, by default, QUOTED_IDENTIFIER will be on but this is not the case for SQL Server Agent Job Step.

So for the Steps that are running Delete statements as a Job step should have the below code appended to it…

SET QUOTED_IDENTIFIER ON
Go
DELETE STATEMENT

Above change will make your job step to succeed…

Additional Info:

• You can use DBCC USEROPTIONS which returns the SET options active for the current connection.
• You can also enable Quoted_Identifier to ON at Server Level by clicking on Server properties Connections TabCheck “Quoted Identifier” Click Ok.