SQL Agent – Disable All Jobs (script)

Another addition to your cookbook, comes in very handy during releases!

The scripts below are easier, but if you want to avoid updating system tables directly…please scroll down.

————————————————–

Disable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Enable All SQL Server Agent Jobs
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE Enabled = 0;
GO

Disable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'Admin%';
GO

Enable Jobs By Job Name
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 1
WHERE [Name] LIKE 'Admin%';
GO

————————————————–

Disable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 0;
GO

Enable Jobs By Job Name
USE msdb ;
GO

EXEC dbo.sp_update_job
@job_name = N'SomeJob',
@enabled = 1;
GO

Disable All ENABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 1 --Optional filter
ORDER BY s.name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM @Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 0
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

Enable All DISABLED SQL Server Agent Jobs

SET NOCOUNT ON

DECLARE @Job_Names TABLE
(
Job_Name SYSNAME NOT NULL
)

INSERT INTO @Job_Names
SELECT s.name
FROM msdb.dbo.sysjobs s
WHERE s.Enabled = 0 --Optional filter
ORDER BY s.name

DECLARE @job_name SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER

DECLARE disable_jobs CURSOR FOR
SELECT Job_Name
FROM @Job_Names

SET @job_id = NULL

OPEN disable_jobs
FETCH NEXT FROM disable_jobs INTO @job_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT
EXEC msdb.dbo.sp_update_job @job_id, @enabled = 1
SET @job_id = NULL
FETCH NEXT FROM disable_jobs INTO @job_name

END

CLOSE disable_jobs
DEALLOCATE disable_jobs

How to log all SQL Server and SQL Agent starts

SQL Server

To log all SQL Server starts, simply execute the T-SQL code provided below to create a new table and Stored Procedure (SP) to log this event:

USE LoggingDB
GO
CREATE TABLE dbo.SQLServerStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_ServerStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLServerStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLServerStartTimes DEFAULT VALUES
END
GO

To trigger/execute this SP once SQL Server starts, use this statement (more on MSDN about this built-in Stored Procedure)
EXEC SP_PROCOPTION sp_LogSQLServerStart, 'STARTUP', 'ON'
GO

Result:

SQL Agent

For SQL Agent starts, simply execute the T-SQL code provided below to create a new table and SP to log this event:


USE LoggingDB
GO
CREATE TABLE dbo.SQLAgentStartTimes
(
LogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
StartTime DATETIME NOT NULL
CONSTRAINT DF_SQLAgentStartTimes_StartTime DEFAULT GETDATE()
)

USE master
GO
CREATE PROCEDURE dbo.sp_LogSQLAgentStart
AS
BEGIN

SET NOCOUNT ON

PRINT '*** LOGGING SERVER STARTUP TIME ***'
INSERT INTO LoggingDB.dbo.SQLAgentStartTimes DEFAULT VALUES
END

Now, since all coding is done…we still need to setup a new SQL Agent job to execute this SP once SQL Agent starts.
Create a new job in SQL Agent and click on Schedules, select

Result:

Special thanks to Armando Prato for the inspiration!

Best Practices – Recycle SQL Server Error Logs

It’s been a while since my previous post, was working on my own database server. More info on that soon!

Today I’ve received a great tip from my colleague Berrie Roelofs (experienced DBA) to monitor and recycle Error Logs often (like once a week or so) on database servers which are continuously running in a Production Environment.

In most production environments, the SQL Server is restarted very rarely as a result both SQL Server Error Log and SQL Server Agent Log keeps growing and at times it becomes very difficult to open up and analyze Error Logs when you encounter issues. However, it will be a good practice for a DBA to schedule a SQL Server Agent Job which runs once in a week to execute system stored procedures to create a new SQL Server Agent Error Log and clear the SQL Server Error Logs.

SQL Server Agent Error Log (SQLAGENT.OUT)

The SQL Server Agent Error Log is a great place to find information about what is happening in your SQL Server Agent. Each SQL Server Agent Error log will record informational, warnings and different error messages which have occurred since SQL Server Agent was last restarted or since the last time you have recycled the SQL Server Agent Error Log.

By default, the SQL Server Agent Error Log is located in “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\SQLAGENT.OUT”. A new error log is created when an instance of SQL Server Agent is restarted. Database Administrators can execute sp_cycle_agent_errorlog system stored procedure to recycle the SQL Server Agent Error Logs without recycling the instance of SQL Server Agent.

How to recycle the SQL Server Agent Error Log

Option 1

Use the GUI of SSMS (or SSDT in SQL Server 2012) to recycle it manually:

(Right-Click on ‘Error Logs’)

Option 2

Schedule a SQL Agent Job that recycles the SQL Server Agent Log:
(Execute permissions for sp_cycle_agent_errorlog are restricted to members of the sysadmin fixed server role.)

MSDN: sp_cycle_agent_errorlog

EXEC dbo.sp_cycle_agent_errorlog ;
GO

SQL Server Error Logs

By default, SQL Server will keep six archived error log files along with the error log that is currently used. However, you can override this number if you wish to archive fewer or more logs. This can be accomplished by right clicking on SQL Server Logs within Enterprise Manager and choosing “Configure”.

The SQL Server Error Logs are located in “Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG.n”.

The most current error log file is called ERRORLOG. If you stop and re-start SQL Server, the old log will be archived and a new one will be created.
Or use T-SQL to locate the Error Logs:

SELECT SERVERPROPERTY('ErrorLogFileName');

Recycle the Error Log
MSDN: sp_cycle_errorlog

EXEC sp_cycle_errorlog;
GO

This causes a new error log file to be created and will prevent the error log becoming overly large on systems that do not reboot for a long time.

Are you using these features on your production server? Let me know.

Automated query performance test

Just got back from San José and San Fransisco!
Went there for two weeks to work on my project for LumiLEDs (Philips Lightning).

A great view on the Golden Gate Bridge!

 
I did something new: needed to test my new Stored Procedures (SP’s) on performance and make an comparison between the ‘new’ and the ‘old’ queries.
For this blog post I rewrote my original code to use AdventureWorksLT2008R2, but it’s easy to rewrite it for AdventureWorksLT.

Like a lot of things you start with an idea:

The basics:
Test two scenarios (S1 – old vs. S2 – new) on performance with a common set of ID’s (TestIDs table) and log the results (TestResults table). Each scenario has three queries/SP’s (Q1 to Q3) with the same resultset, but in this example they also share a common query.

The objects:

 
For this test I made two tables:
TestIDs, which contains a common set of IDs to test:

TestResults, well….for the results 🙂

 
Each scenario has three queries (Q1 to Q3) to test, so in total I’ve made six SP’s.
For each scenario I made a SP to run the individual SP’s with a cursor called ‘sp_TestScript_S#‘.

 
How to start:
Execute the generated script to create all the objects:
Test_scripts

Don’t forget to install/attach AdventureWorksLT(2008R2).

Execute the SP called: dbo.sp_Populate_tbl_TestIDs
The table TestIDs should be filled with ID’s by now.

Now you need to execute both SP’s (‘sp_TestScript_S#‘) at the same time.
You may want to use SQL Agent Jobs and schedule them.

 
The results:
As soon as the SQL Agent Jobs are finished, have a look at the results.
Execute the SP called dbo.sp_TestResults and analyse the results:

For a more detailed overview execute the SP called dbo.sp_TestResult_Details.
In the preview below, I zoomed in on ‘sp_S2_Q3_ProductModelID’:

 
Conclusion:
In my development environment I first tested my new queries and took the ID’s with the longest processing time or the highest result count.
 
You may want to combine:
1000 x Max(ProcessTime)
+
1000 x Max(ResultCount)

Next: use these ID’s to fill up the TestIDs table (sort of a worst case scenario) and run both SQL Agent Jobs (S1 and S2) simultaneously.
 
Have fun with testing! 😀