MS SQL Server 2008 R2, Tech Notes, Uncategorized

Disable then Enable SQL Agent Jobs

2016-01-22 Jon Culp Leave a comment

This script has three actions:
1) Put Current Job Information Into Temp Table
2) DISABLE All SQL Server Agent Jobs
3) ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
Note: If you need to disconnect from the server after step 1 then step 3 will not be able to read from the temp table. Instead, you would have to feed a list of job IDs to the query. I have created a place for this just in case this is what you have to do.

Test Results:
Put Current Job Information Into Temp Table: 47 row(s) affected
DISABLE All SQL Server Agent Jobs: 33 row(s) affected
ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: 33 row(s) affected

SET NOCOUNT ON

USE MSDB;

--Put Current Job Information Into Temp Table
SELECT * 
INTO #SYSJOBS	/* DROP TABLE #SYSJOBS */
FROM MSDB.dbo.sysjobs 
PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM #SYSJOBS
;

--DISABLE All SQL Server Agent Jobs
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1
PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs
;

--ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling
UPDATE A
SET Enabled = 1
FROM MSDB.dbo.sysjobs A
WHERE Enabled = 0
AND (
	EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/
	OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/
	)
PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount)  +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs
;

SET NOCOUNT OFF

SET NOCOUNT ON

USE MSDB;

--Put Current Job Information Into Temp Table

SELECT *

INTO #SYSJOBS /* DROP TABLE #SYSJOBS */

FROM MSDB.dbo.sysjobs

PRINT 'Put Current Job Information Into Temp Table: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM #SYSJOBS

;

--DISABLE All SQL Server Agent Jobs

UPDATE MSDB.dbo.sysjobs

SET Enabled = 0

WHERE Enabled = 1

PRINT 'DISABLE All SQL Server Agent Jobs: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs

;

--ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling

UPDATE A

SET Enabled = 1

FROM MSDB.dbo.sysjobs A

WHERE Enabled = 0

AND (

EXISTS (SELECT 1 FROM #SYSJOBS B WHERE B.enabled = 1 AND A.job_id = B.job_id) /*READ FROM TEMP TABLE*/

OR CONVERT(varchar(MAX),job_id) IN ('') /*MANUALLY LIST JOB IDS*/

)

PRINT 'ENABLE SQL Server Agent Jobs Which Were Enabled Prior to Disabling: ' + CONVERT(varchar,@@rowcount) +' row(s) affected'

SELECT * FROM MSDB.dbo.sysjobs

;

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes

Get and Kill Executing Processes of Job

2016-01-19 Jon Culp Leave a comment

This query will identify the current SQL processes or executions which belong to a specific job.

SELECT *
FROM master.dbo.sysprocesses p
JOIN msdb.dbo.sysjobs J
	ON UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))
	= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), J.Job_Id),1))
WHERE J.name = 'Some Job'

SELECT *

FROM master.dbo.sysprocesses p

JOIN msdb.dbo.sysjobs J

ON UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))

= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), J.Job_Id),1))

WHERE J.name = 'Some Job'

We had a job process that was getting blocked for long periods, and in turn was causing a lot of blocking. The best solution here is to fix the root blocker, however, as a short term fix we want to allow the job to run but then kill it a specific time if it is still running. To do this, we create another job that looks for all of the SPIDS associated to executions which belong to the job getting blocked, and execute a kill commend for each SPID.

SET NOCOUNT ON

--DECLARE VARIABLES
DECLARE @Job_ID UNIQUEIDENTIFIER = 'YOUR JOB ID'
DECLARE @tbl_SPIDS TABLE (SPID SMALLINT)
DECLARE @SPID SMALLINT
DECLARE @SQL VARCHAR(MAX)

--GET ALL SPIDS FOR ACTIVE JOB PROCESSES
INSERT INTO @tbl_SPIDS ( SPID )

SELECT p.spid
FROM   master.dbo.sysprocesses p
WHERE UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))
    = UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), @Job_Id),1))

--KILL ACTIVE JOB PROCESSES
WHILE EXISTS (SELECT TOP 1 1 FROM @tbl_SPIDS)
BEGIN
	SET @SPID = (SELECT TOP 1 SPID FROM @tbl_SPIDS)
	SET @SQL = 'KILL '+CONVERT(VARCHAR(10),@SPID)
	EXEC (@SQL)
	DELETE FROM @tbl_SPIDS WHERE SPID = @SPID
END	

SET NOCOUNT OFF

SET NOCOUNT ON

--DECLARE VARIABLES

DECLARE @Job_ID UNIQUEIDENTIFIER = 'YOUR JOB ID'

DECLARE @tbl_SPIDS TABLE (SPID SMALLINT)

DECLARE @SPID SMALLINT

DECLARE @SQL VARCHAR(MAX)

--GET ALL SPIDS FOR ACTIVE JOB PROCESSES

INSERT INTO @tbl_SPIDS ( SPID )

SELECT p.spid

FROM master.dbo.sysprocesses p

WHERE UPPER(SUBSTRING(replace(p.program_name, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34))

= UPPER(CONVERT(VARCHAR(34),convert(varbinary(16), @Job_Id),1))

--KILL ACTIVE JOB PROCESSES

WHILE EXISTS (SELECT TOP 1 1 FROM @tbl_SPIDS)

BEGIN

SET @SPID = (SELECT TOP 1 SPID FROM @tbl_SPIDS)

SET @SQL = 'KILL '+CONVERT(VARCHAR(10),@SPID)

EXEC (@SQL)

DELETE FROM @tbl_SPIDS WHERE SPID = @SPID

END

SET NOCOUNT OFF

Tech Notes, Windows

Enable Windows Administrator Account

2016-01-10 Jon Culp Leave a comment

Windows Vista and up has the administrator account disabled by default. In order to use it you have to enable it. One easy method I found was to use the command prompt, aka CMD.

1. Open command prompt (CMD) in administrator mode.

Windows < 8: Start > Accessories > Right Click on Command Prompt > Run as Administrator.
Windows >= 8: Right Click on Start > Open Command Prompt as Administrator

2. Execute this command to Enable:

net user administrator /active:yes

1	net user administrator /active:yes

Next time you log in you should see a new option for administrator. I would recommend setting the administrator password if you have not already done so.

If you decide you want to disable the administrator account, execute this command:

net user administrator /active:no

1	net user administrator /active:no

Links & Lists

2016 Learning Goals

2016-01-07 Jon Culp Leave a comment

MCSA & MCSE SQL Server Certification

MCSA: SQL Server
- 70-461 – Querying Microsoft SQL Server 2012
  - https://www.udemy.com/microsoft-sql-server-2012-certification-70-461/
- 70-462 – Administering Microsoft SQL Server 2012 Databases
- 70-463 – Implementing a Data Warehouse with Microsoft SQL Server 2012
MCSE: Data Platform
- 70-464 – Developing Microsoft SQL Server Databases
- 70-465 – Designing Solutions for SQL Server
MCSE: Business Intelligence
- 70-466 – Implementing Data Models and Reports with Microsoft SQL Server
- 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server

Neural Networks and Machine Learning

Training Sources:

Coursera
- https://www.coursera.org/course/neuralnets
- 8 weeks of study
- 7-9 hours/week
Udemy
- https://www.udemy.com/neural-networks_in_matlab/
- 23 Lectures and 2 hours of content

Other Interests

Internet of Things Development
Mobile Application Development
Python
Ruby on Rails
Node.js, MongoDB, and Express
Quartz Composer
Actuary Exams

Jon Culp [.com]

Monthly Archives: January 2016