Advertisements

SQL DBA – Query to check Status of last running SQL Jobs


 
To check the status of current running jobs you can use the “Job Activity Monitor” feature in SSMS, but sometimes opening and refreshing the tool takes time.

Here is a simple query you can run in SSMS or from any custom tool to get the status of current running jobs:
 

–> Query #1: This query uses sysjobs view to get the list of all jobs and sysjobhistory view to get the latest status record.

SELECT 
	j.name AS JobName
	,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */  
          +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */  
          + (run_time - (run_time/100) * 100)  /* secs */
           ,CONVERT(DATETIME,RTRIM(run_date),113)),100) AS RunTimeStamp
	,CASE 
		WHEN j.enabled = 1 THEN 'Enabled'  
		ELSE 'Disabled'  
	END JobStatus
	,CASE 
		WHEN jh.run_status = 0 THEN 'Failed'
		WHEN jh.run_status = 1 THEN 'Succeeded'
		WHEN jh.run_status = 2 THEN 'Retry'
		WHEN jh.run_status = 3 THEN 'Cancelled'
		ELSE 'Unknown'  
	END JobRunStatus
FROM msdb.dbo.sysjobs j
OUTER APPLY(
	SELECT TOP 1 * 
	FROM msdb.dbo.sysjobhistory jh
	WHERE jh.job_id = j.job_id AND jh.step_id = 0 
	order by jh.instance_id DESC) jh
where j.name like 'ADF%'
ORDER BY j.name, jh.run_date, jh.run_time 

 

–> Query #2; This query uses sysjobs view to get the list of all jobs and sysjobservers view to get the current status of job.

SELECT 
	j.name AS JobName
	,IIF(js.last_run_date > 0, 
		DATETIMEFROMPARTS(js.last_run_date/10000, js.last_run_date/100%100, js.last_run_date%100, 
		js.last_run_time/10000, js.last_run_time/100%100, js.last_run_time%100, 0), 
		NULL) AS RunTimeStamp
	,CASE 
		WHEN j.enabled = 1 THEN 'Enabled'  
		ELSE 'Disabled'  
	END JobStatus
	,CASE 
		WHEN js.last_run_outcome = 0 THEN 'Failed'
		WHEN js.last_run_outcome = 1 THEN 'Succeeded'
		WHEN js.last_run_outcome = 2 THEN 'Retry'
		WHEN js.last_run_outcome = 3 THEN 'Cancelled'
		ELSE 'Unknown'  
	END JobRunStatus
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobservers js on js.job_id = j.job_id
where j.name like 'ADF%'
ORDER BY j.name, js.last_run_date, js.last_run_time 

 

And if you noted in both the queries above, I’ve used different way to calculate the RunTimeStamp, first by parsing the rum_time column, second by using DateTimeFromParts() function.


Advertisements
Categories: DBA Stuff Tags: ,

SQL DBA – SQL Agent Job history not showing or vanishing away


 
This happened when we started working on a new SQL Server instance for our DEV environment. The history of SQL jobs was not getting retained after a day or few runs.

I checked on the SQL Agent Properties and found that there are some config values set which were very low:
jobhistory_max_rows = 10000
jobhistory_max_rows_per_job = 100

So I checked on MSDN and found that the max permissible values and set them to 999999 & 49999 respectively.

–> This can also be changed by below T-SQL Query by using the system SP sp_set_sqlagent_properties:

USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties 
	@jobhistory_max_rows=999999, 
	@jobhistory_max_rows_per_job=49999
GO

Categories: DBA Stuff Tags: ,

SQL DBA – Stop multiple running SQL Jobs at once


 
Just like my previous post on Enable/Disable multiple SQL Jobs [link], here I will show how can we stop multiple jobs running by SQL Agent at once.

–> Although you can very well Stop a SQL Job in SSMS by simple right-clicking on it. But if you have multiple SQL Jobs running, to stop each of them will be a cumbersome and time taking task.

I will show how this can be done by a T-SQL query:

USE msdb;
GO

-- Stop Multiple running SQL Jobs at once:
DECLARE @dynSql NVARCHAR(MAX) = ''

SELECT @dynSql += N' msdb.dbo.sp_stop_job @job_name = ''' + j.name + N'''' 
			+ CHAR(10) + CHAR(13)
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity AS ja 
ON ja.job_id = j.job_id
WHERE ja.start_execution_date IS NOT NULL
AND ja.stop_execution_date IS NULL
ORDER BY j.name;

PRINT @dynSql;
GO

Simply Copy-Paste the the above Dynamically generated SQL Script and Execute it, it will Stop all Running SQL Jobs at once.


Categories: DBA Stuff, SQL Tips Tags: ,

DACPAC – Could not deploy package, the database platform service with type Sql130DatabaseSchemaProvider is not valid


 
So, today while deploying a DACPAC file by using SqlPackage.exe utility I faced an error:

*** Could not deploy package
Internal error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.

For few minutes I was wondering why this error was coming, but when I saw the version number in above error message it became clear. The version number 130 highlighted above belongs to SQL Server 2016, and the database where I need to deploy DACPAC was SQL Server 2012. So, its clear that the DACPAC was built in Visual Studio with SQL Server 2016 setting.

So I went back to Visual Studio – Project Settings and changed the version to SQL Server 2012, and re-build the Project, and got the correct DACPAC file.


Categories: Uncategorized

SQL Error – Cannot connect to xyz_sql_instance, due to remote firewall issue


 
After creating a new Azure Windows Server VM with SQL Server installed, I was not able to connect it from my PC via SSMS. Everytime I tried to connect I was getting following error:

Cannot connect to xyz_sql_instance.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provier, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
The network path was not found


 

–> I checked online and found following command to enable the port, but it ran into error:

C:\windows\system32>netsh firewall set portopening TCP 1433 “SQLServer”

“… netsh firewall” is deprecated; use “netsh advfirewall firewall” instead.


 

–> So, lets execute the new netsh advfirewall firewall command and see what options it has:

C:\windows\system32>netsh advfirewall firewall /?

The following commands are available:

Commands in this context:
? – Displays a list of commands.
add – Adds a new inbound or outbound firewall rule.
delete – Deletes all matching firewall rules.
dump – Displays a configuration script.
help – Displays a list of commands.
set – Sets new values for properties of a existing rule.
show – Displays a specified firewall rule.

To view help for a command, type the command, followed by a space, and then type ?.

 

–> Now with above options we are clear that we have to add a new inbound firewall rule, so we will check how can we use the add option:

C:\windows\system32>netsh advfirewall firewall add /?

The following commands are available:

Commands in this context:
add rule – Adds a new inbound or outbound firewall rule.

 

–> Let’s apply the add rule option and see more options:

C:\windows\system32>netsh advfirewall firewall add rule /?

Usage: add rule name=
dir=in|out
action=allow|block|bypass
[program=]
[service=|any]
[description=]
[enable=yes|no (default=yes)]
[profile=public|private|domain|any[,…]]
[localip=any|||||]
[remoteip=any|localsubnet|dns|dhcp|wins|defaultgateway|
||||]
[localport=0-65535|[,…]|RPC|RPC-EPMap|IPHTTPS|any (default=any)]
[remoteport=0-65535|[,…]|any (default=any)]
[protocol=0-255|icmpv4|icmpv6|icmpv4:type,code|icmpv6:type,code|
tcp|udp|any (default=any)]
[interfacetype=wireless|lan|ras|any]
[rmtcomputergrp=]
[rmtusrgrp=]
[edge=yes|deferapp|deferuser|no (default=no)]
[security=authenticate|authenc|authdynenc|authnoencap|notrequired
(default=notrequired)]

Remarks:

– Add a new inbound or outbound rule to the firewall policy.
– Rule name should be unique and cannot be “all”.
– If a remote computer or user group is specified, security must be
authenticate, authenc, authdynenc, or authnoencap.
– Setting security to authdynenc allows systems to dynamically
negotiate the use of encryption for traffic that matches
a given Windows Firewall rule. Encryption is negotiated based on
existing connection security rule properties. This option
enables the ability of a machine to accept the first TCP
or UDP packet of an inbound IPsec connection as long as
it is secured, but not encrypted, using IPsec.
Once the first packet is processed, the server will
re-negotiate the connection and upgrade it so that
all subsequent communications are fully encrypted.
– If action=bypass, the remote computer group must be specified when dir=in.
– If service=any, the rule applies only to services.
– ICMP type or code can be “any”.
– Edge can only be specified for inbound rules.
– AuthEnc and authnoencap cannot be used together.
– Authdynenc is valid only when dir=in.
– When authnoencap is set, the security=authenticate option becomes an
optional parameter.

Examples:

Add an inbound rule with no encapsulation security for browser.exe:
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\programfiles\browser\browser.exe”
security=authnoencap action=allow

Add an outbound rule for port 80:
netsh advfirewall firewall add rule name=”allow80″
protocol=TCP dir=out localport=80 action=block

Add an inbound rule requiring security and encryption
for TCP port 80 traffic:
netsh advfirewall firewall add rule
name=”Require Encryption for Inbound TCP/80″
protocol=TCP dir=in localport=80 security=authdynenc
action=allow

Add an inbound rule for browser.exe and require security
netsh advfirewall firewall add rule name=”allow browser”
dir=in program=”c:\program files\browser\browser.exe”
security=authenticate action=allow

Add an authenticated firewall bypass rule for group
acmedomain\scanners identified by a SDDL string:
netsh advfirewall firewall add rule name=”allow scanners”
dir=in rmtcomputergrp= action=bypass
security=authenticate

Add an outbound allow rule for local ports 5000-5010 for udp-
Add rule name=”Allow port range” dir=out protocol=udp localport=5000-5010 action=allow

 

–> OK, so this gives us more options and also samples at the end to construct our command. So we will us the above highlighted command to add port for our SQL Server, as shown below:

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Engine” protocol=TCP dir=in localport=1433 action=allow

C:\windows\system32>netsh advfirewall firewall add rule name=”SQL Server Browser” protocol=UDP dir=in localport=1434 action=allow
 

Now, I am able to connect remotely without any issues.


Categories: SQL Errors