RSS

Category Archives: SQl server Administration

Improve SQL Server Performance with Compression

Improve SQL Server Performance with Compression

SQL Server data compression is now available in all editions of SQL Server, starting with 2016 SP1.

In this Article, you will not only know how data compression will save space, you’ll also find out how compression can sometimes improve performance as well.

Space Savings vs. Performance

When I first heard about compression back in 2008, my first thought is that it would have a big performance penalty. Compression would save disk space, but it would probably decrease performance as the data was compressed and decompressed. It turns out that compression can improve performance instead. Because compressed data fits in a smaller number of data pages, there are decreased I/O requirements. Since I/O is generally the bottleneck in SQL Server, this can improve performance. Compressed data also has a decreased memory requirement. When querying compressed data, a smaller number of pages will be copied to the buffer pool. The one area that is impacted is CPU. You do need to have some CPU headroom because compression will require some additional CPU resources.

a1

The good thing is that if the workload is reasonably tuned, many SQL Server instances have more CPU resources than they need. One note of caution here. Don’t look to compression as the solution to solving major performance issues. You need to look at physical resources, configuration, indexing, and query tuning. The point I’m trying to make is that decreased I/O and better memory utilization will benefit the workload in many cases.

Availability

Compression has been available in SQL Server for all versions from 2008 to 2016 SQL server, but only for Enterprise Edition. Beginning with SQL Server 2016 SP1, it’s now available in Enterprise, Standard, and Express. I’m excited about this because now compression is within the reach of any organization.

Compression Types

There are two types of compression that you can use to compress a table, index, or even a partition:

  • Row Level:

  • Row level compression works by storing fixed-width data types as variable length data types. Nulls and zeroes do not take any space. For example, in an uncompressed table, an integer column takes 4 bytes per row, even for those rows that have a small number such as 1 or 10, or even null. Once row compression is implemented on the table, each value will be stored with the smallest possible number of bytes. So outside of the metadata, storing a 1 in an integer column will take 1 byte, storing a null or 0 will take 0 bytes.

 a2

  • Starting with SQL Server 2012, Unicode compression is implemented when you use row compression. This applies to Unicode columns in varchar and in char. In an uncompressed table, each character takes up 2 bytes in a Unicode column, even if a small character set is used. In those cases, compressing the table will store the Unicode characters in 1 byte instead.

a3

  • In the technical article, Data Compression: Strategy, Capacity Planning, and Best Practices, Microsoft recommends using row compression on all data, as long as you have 10% extra CPU capacity, and, of course, as long as you achieve some space savings.
  • It costs Low CPU penalty.
  • Page Level:

  • When you implement page level compression, the rows are automatically row-compressed first.
  • Page level compression also removes repeated data within a page by two mechanisms, prefix and dictionary compression.
  • Page level compression can compress a table to a smaller size than row compression, but it is recommended for tables that are mostly inserted, but not updated that often.
  • It does have a higher CPU penalty.

Neither of these compression types will work on row-overflow data. This is data from a row that exceeds 8K. For example, you can create a table that has two varchar 8000 columns. If a row exceeds 8060 bytes, then SQL Server will move one or more of those columns to another page so that the row fits. One way to get around this issue is the new COMPRESS function.

COMPRESS Function

  • You can use this function to compress individual values, which includes row-overflow data.
  • It uses a GZIP algorithm to compress the values. There is a downside to this, however. The COMPRESS function must be applied each time a value is inserted or updated, and a DECOMPRESS function must then be used to read the value. This means that there would be changes to the application or stored procedure.

Performance Metrics

In this demo, we’ll do some performance comparisons. We’ll take a close look at I/O and memory impact. We’ll see how compression affects both reads and writes to the data.

  • Select comparison

I created two tables; one with page compression called “bigTransactionHistoryPAGE” and one with row compression called “bigTransactionHistoryROW”. I also created a table with no compression called “bigTransactionHistory” that I’ll use during this demonstration as well. And finally, I populated the two compressed tables with around 31 million records.

Let’s take a look at the number of pages in each table or index:

SELECT OBJECT_NAME(i.[object_id]) AS TableName,

       i.name AS IndexName, SUM(s.used_page_count) IndexPages,

       FORMAT(1 - SUM(s.used_page_count) * 1.0/CASE WHEN i.name LIKE 'IX%' THEN 131819 ELSE 143645 END, 'P') AS PercentSaved

FROM sys.dm_db_partition_stats  AS s

JOIN sys.indexes AS i

ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

WHERE OBJECT_NAME(i.[object_id]) LIKE 'bigTransactionHistory%'

       AND OBJECT_NAME(i.[object_id]) <> 'bigTransactionHistoryTEST'

GROUP BY i.[object_id],i.name

ORDER BY IndexPages Desc;

a4

The uncompressed table from is the largest. The non-clustered index compressed with row compression saved about 30% of the space, and the clustered index saved about 37%. The page compressed table is really interesting. The clustered index is much smaller than the original table, while the non-clustered index is about 39% smaller.

I’m going to turn on STATISTICS IO to compare the number of pages touched when I query each table:

SET STATISTICS IO ON;

GO

SELECT SUM(Quantity) AS ItemsPurchased

FROM bigTransactionHistory;




SELECT SUM(Quantity) AS ItemsPurchased

FROM bigTransactionHistoryROW;




SELECT SUM(Quantity) AS ItemsPurchased

FROM bigTransactionHistoryPAGE;

a5

You see here that less I/O is needed for the compressed table. That’s actually obvious since the clustered and non-clustered indexes are smaller when compressed.

The next query shows how many pages are in the buffer for each index:

SELECT COUNT(*)AS cached_pages_count

,name ,index_id

FROM sys.dm_os_buffer_descriptors AS bd

INNER JOIN

(

SELECT object_name(object_id) AS name

,index_id ,allocation_unit_id

FROM sys.allocation_units AS au

INNER JOIN sys.partitions AS p

ON au.container_id = p.hobt_id

AND (au.type = 1 OR au.type = 3)

UNION ALL

SELECT object_name(object_id) AS name

,index_id, allocation_unit_id

FROM sys.allocation_units AS au

INNER JOIN sys.partitions AS p

ON au.container_id = p.partition_id

AND au.type = 2

) AS obj

ON bd.allocation_unit_id = obj.allocation_unit_id

WHERE database_id = DB_ID()  AND name LIKE 'bigTransactionHistory%'

GROUP BY name, index_id

ORDER BY cached_pages_count DESC;

a6

Again, when the index resides on fewer pages, the data takes less space and memory. Notice that the clustered index was used for the page-compressed table. We saw that the page-compressed clustered index is much smaller than the non-clustered index.

On this Azure VM, I have seen inconsistent results in the time to run the queries. For example, if I use DBCC DROPCLEANBUFFERS, or even restart SQL Server to ensure the data must be loaded from disk, one of these queries could run anywhere from 1 second to even a minute. I suspect that the VM is sharing resources with other VMs, and that is causing my discrepancies. I decided to run the queries each in a loop to better see the difference. Inside the loop, I use DBCC DROPCLEANBUFFERS to make sure that the data was loaded from disk, and not cached data.

DECLARE @Count INT = 0;

WHILE @Count < 100 BEGIN

       DBCC DROPCLEANBUFFERS;    

       SELECT SUM(Quantity) AS ItemsPurchased

       FROM bigTransactionHistory;

       SET @Count += 1;

END;

The loop against the uncompressed table took 3 minutes and 31 seconds. The row-compressed table took 3 minutes and 4 seconds and the page compressed table took 3 minutes and 18 seconds. So in this case, the row-compressed query performed the best, with the page-compressed query next.

  • Insert comparison

Let’s take a look at inserting data. In this script, I populated an uncompressed table and our two compressed tables with a loop of 1000 inserts of 1000 rows each. To make things fair, I ran DROPCLEANBUFFERS before each loop.

DBCC DROPCLEANBUFFERS;

GO

--2:40

DECLARE @Count INT = 0;

WHILE @Count < 1000 BEGIN

INSERT INTO dbo.bigTransactionHistoryTEST

SELECT *

FROM bigTransactionHistory

WHERE TransactionID BETWEEN @Count * 1000 + 1 AND (@Count + 1) * 1000;

SET @Count = @Count + 1;

END;

The uncompressed inserts took 2 minutes and 40 seconds. The row-compressed inserts completed a bit faster at 2 minutes and 23 seconds, and the page-compressed inserts completed in 2 minutes and 14 seconds. The page-compressed inserts were actually fastest, and I suspect that it might be due to less I/O.

  • Update comparison

Let’s take a look at updates.

DBCC DROPCLEANBUFFERS;

GO

–1:48

DECLARE @Count INT = 0;

WHILE @Count < 1000 BEGIN

UPDATE dbo.bigTransactionHistoryTEST

SET Quantity = Quantity + 1

WHERE TransactionID BETWEEN @Count * 1000 + 1 AND (@Count + 1) * 1000;

SET @Count = @Count + 1;

END;

Again, I ran loops. In this case, it’s 1000 updates of 1000 rows Updating the uncompressed table took 1 minute and 48 seconds. Updating the row-compressed table took 1 minute and 36 seconds. The page-compressed table was much slower at 2 minutes and 21 seconds. During the slides, I mentioned that Microsoft recommends page compression for workloads that have few updates, and you can see that the updates are slower for page compression.

To summarize, I saw decent space savings, especially with page compression. The compressed tables performed better for both selects and inserts. Updates, however, performed noticeably worse with page compression.

a7

Summary:

This Article is an overview of data compression. Compression is now available in all editions of SQL Server, starting with 2016 SP1. You can implement row and page level compression, and also use the new COMPRESS function. Compression can improve performance because of decreased I/O and memory pressure. I hope this article has been informative for you.

Useful Links:

 

Tags: , , , ,

Alwayson Availability Groups for Disaster Recovery Solutions

Introduction

There are many users, who are not aware about disaster recovery planning with always-on availability groups. Even they do not know the terms that come in the utilization of Always-On Availability groups for disaster recovery planning. In the following section, we will discuss about the always-on availability groups for resolving the disaster Read the rest of this entry »

 
 

Tags:

Help! I have -2, -3, or -4 Session ID!

We can kill a session by using KILL command. However, KILL command requires a positive number; executing KILL with negative number returns an error:

Msg 6101, Level 16, State 1, Line 1
Session ID -4 is not valid.

In order to kill the session ID, you need to find the unit of work (UOW) guid.

SELECT DISTINCT(request_owner_guid) AS UOW
  FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2,-3,-4)

Now you can kill this using UOW:

KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'

Like all normal transactions, killing a session causes any work performed by it to be rolled back to bring the database back into consistent state.

The negative session ID are orphaned or stuck sessions that SQL Server; they are rare occurrences. Most often the only one I have seen is -2; what do they mean?

Session ID Description
-2 The blocking resource is owned by an orphaned distributed transaction.
-3 The blocking resource is owned by a deferred recovery transaction.
-4 Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

Reference: Books Online, sys.sysprocesses (Transact-SQL)

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

 
Leave a comment

Posted by on June 22, 2016 in SQl server Administration

 

Tags: , , , ,

Awesome!! DMV and Stored procedure for any DBA

 Untitled Hello every body today no new post today but i will share some amazing and Awesome!!  DMV and stored i created it to help me in my daily work really it is very helpful for any Database Administrator
  • Create Clustered Index on all HEAP Tables by on Single Click.

this one of the best DMV for me because it fast the process for me and it is very helpful by this Stored procedure i sued the technique of   SQL SERVER DYNAMIC QUERY to loop on all databases exists on my SQL SERVER CLUSTER where this databases is not in system databases and it should be with status online to retrieve from it

  1. list by all heap tables.
  2. Loop in this list to retrieve the best column can be Clustered index Based on the criteria that have been developed from my side (Customized option )
  3. build the T-SQL statment of create clustered index

For more information and for download DMV check the (POST &  VIDEO)

  • Index Statistics for all Databases Exists on your Server

NOW any DBA can return all his index Statistics and save it to decide what he need to do shall i should drop some index (UNUSED INDEX) or shall i should check the index size to take A wise decision we should have the information about the index (last user seek data and percentage , user scan , index size , drop index statement if you need to delete index,..ETC)

For more information and for download DMV check the (POST)

Read the rest of this entry »

 
 

Tags: , , , , , , , , , , , , , ,

DMV Stacks#1(How to check Database Size , Log Size , Free Size)

Hi dears today i will write small DMV but the usage of it is very high needful and important today our DMV is How to check the Database size , Log Size , Free Size in SQL Server i will not Speak more let my DMV Complete what i need to Say :

at the first i will Use in my Demo Database AdventureWorkyou can download it from here SQL Server Database Product Simple

1- Check Database Size for one DB :

by this DMV you can find the Database Size by MB or GB for one DB on your Database Server and you can know the Physical File located for this DB

SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'AdventureWorks'

DatabaseSize_1

 

2- Database Size For All Databases on your DB Server :

here we can get all database size for all DB Server Each database separately

SELECT d.NAME,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs ,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id GROUP BY d.NAME
Order by (SUM(mf.size) * 8 / 1024) DESC

IF you need to know the SUM of the Database size it’s very simple the same previous DMV with CTE (Conman table Expression Technology )

Note : i used here in this DMV Concat Function  this new Function in SQL Server 2012 by it we do Concatenation between integer and String in the previous SQL Server version you should Convert the integer to String at first now it very easy

With CTE AS (
SELECT d.NAME,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs,
(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs
FROM sys.master_files mf INNER JOIN sys.databases d
ON d.database_id = mf.database_id
GROUP BY d.NAME)
Select Concat (SUM(Size_GBs),' GB') [DB Server SIZEfrom CTE

3- Check Free Size for one Database :

sometimes we need free size at this time we should know How many GB can we free it from our Database Server

SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB,
 size/128.0 - CAST(FILEPROPERTY(name'SpaceUsed'AS INT)/128.0 ASFreeSpaceMB
FROM sys.database_files;

For all Databases  and the SUM free Size :

one single DMV to know all Databases Free size and the SUM of this Frees size using Temp Table

</pre>
Create Table #LogSize
(
Database_name Nvarchar(200),
[FILENAME] Nvarchar(200),
 CurrentSizeMB BIGINT,
 FreeSpaceMB BIGINT
)
insert into #LogSize
exec sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
'
Select *,FreeSpaceMB/1024 AS FreeSpaceGB from #LogSize
ORDER BY FreeSpaceMB Desc
SELECT SUM(FreeSpaceMB/1024) TOTAL_Free_SIZE_GB from #LogSize
drop Table #LogSize
<pre>

4- Check Log Size for all Database or one Database

here i am using temp table to save in it all my Database log size inforamtion and then i can Select what i need based on my requirement

</pre>
Create Table #LogSize
(
Database_name Nvarchar(200),
Log_File_Size_MB Int ,
Log_File_Size_GB INT
)
insert into #LogSize
exec sp_msforeachdb
'use [?];
 select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
(sum(size)/128.0)/1024 AS Log_File_Size_GB
from sys.database_files where type=1
group by type
'
Select from #LogSize where Log_File_Size_MB > 100 order by Log_File_Size_MBdesc
drop Table #LogSize
<pre>

Follow Me
Mostafa Elmasry 

 

How to monitor your SQL Server instances..(4/10)?

Hello every body in the previous post we figured more detail about How to monitor and mange your SQL Server jobs ..?  but now all thing What we Covered in the Previous Post ..? we Covered 10 Point

  1. Check SQL Server jobs First Vision..?
  2. Check jobs Status ..? Is it enabled or Disabled ..?
  3. When was SQL agent job was created or when was last time it was modified..?
  4. Identify newly created SQL Server Agent Jobs
  5. Check enabled jobs Without notification setup ..?
  6. retrieving enabled operators in SQL Server Agent
  7. Update SQL Server Agent Jobs without any notification by New notification
  8. SQL Server Agent Job Setup and Configuration Information..
  9. Check jobs With Enabled Schedule ..
  10. Check SQL Server Agent Job Schedule Information..

and i will going today to complete this Subject by Covering another Impressive point :

  1. Configuring SQL Agent Jobs to Write to Windows Event Log.
  2. Generate SQL Agent Job Schedule Report
  3. SQL Server Agent Job Setup and Configuration Information
  4. SQL Server Agent Job Steps Execution Information
  5. Jobs Report by OnSuccessAction and on_fail_action
  6. Check or Change job owner
  7. List by jobs are ruining now on your DB Server.
  8. Jobs With Execution Long Time .
  9. Select Failed Job
  10. How to search on your Jobs TEXT.

let’ s drill down to figure this 10 new point one by one .

  • Configuring SQL Agent Jobs to Write to Windows Event Log.

at the first we need to know the jobs not configured to write in the event log

SELECT [name]
 FROM msdb..sysjobs
 WHERE [notify_level_eventlog] = 0

Let’s Configured this job to write in the Event log

UPDATE msdb..sysjobs
 SET [notify_level_eventlog] = 2
 --WHERE [name] like '%Your Joba Name%'
  • Generate SQL Agent Job Schedule Report

by this report we can figured the

Name : Job Name

active_start_date : Start time for the job Execute.

ScheduleDscr : Description for the job Schedule

enabled : IF = 1 (job Enabled ) IF = 0 (Job Disable)

to generate this report we have 2 Steps First one Create Function , Second one the DMV using CTE (Conman Table Expression )

CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
 @freq_interval INT ,
 @freq_subday_type INT ,
 @freq_subday_interval INT ,
 @freq_relative_interval INT ,
 @freq_recurrence_factor INT ,
 @active_start_date INT ,
 @active_end_date INT,
 @active_start_time INT ,
 @active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT
IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' CONVERT(NVARCHAR, @active_start_date) + N' at ' CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' right('00' cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' CONVERT(NVARCHAR, cast((@active_start_time / 10000) asvarchar(10)) + ':' right('00' cast((@active_start_time % 10000) / 100 asvarchar(10)),2))
WHEN 0x2 THEN N'every ' CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' +right('00' cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' right('00' cast((@active_end_time % 10000) / 100 as varchar(10)),2) )
RETURN @schedule_description
END

With CTE AS (
SELECT dbo.sysjobs.nameCAST(dbo.sysschedules.active_start_time / 10000 ASVARCHAR(10))
':' RIGHT('00' CAST(dbo.sysschedules.active_start_time % 10000 / 100 ASVARCHAR(10)), 2) AS active_start_time,
dbo.udf_schedule_description(dbo.sysschedules.freq_type,
dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type,
dbo.sysschedules.freq_subday_interval,
dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor,
dbo.sysschedules.active_start_date,
dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time,
dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNERJOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id
Select from CTE
where ScheduleDscr Like '%DAY%'-----Option
  • Information for SQL Server Job Configuration

by this Script we can now all things bout our job Configuration

<em>
SELECT
 [sJOB].[job_id] AS [JobID]
 , [sJOB].[nameAS [JobName]
 , [sDBP].[nameAS [JobOwner]
 , [sCAT].[nameAS [JobCategory]
 , [sJOB].[description] AS [JobDescription]
 CASE [sJOB].[enabled]
 WHEN THEN 'Yes'
 WHEN THEN 'No'
 END AS [IsEnabled]
 , [sJOB].[date_created] AS [JobCreatedOn]
 , [sJOB].[date_modified] AS [JobLastModifiedOn]
 , [sSVR].[nameAS [OriginatingServerName]
 , [sJSTP].[step_id] AS [JobStartStepNo]
 , [sJSTP].[step_name] AS [JobStartStepName]
 CASE
 WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
 ELSE 'Yes'
 END AS [IsScheduled]
 , [sSCH].[schedule_uid] AS [JobScheduleID]
 , [sSCH].[nameAS [JobScheduleName]
 CASE [sJOB].[delete_level]
 WHEN THEN 'Never'
 WHEN THEN 'On Success'
 WHEN THEN 'On Failure'
 WHEN THEN 'On Completion'
 END AS [JobDeletionCriterion]
FROM
 [msdb].[dbo].[sysjobs] AS [sJOB]
 LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
 ON [sJOB].[originating_server_id] = [sSVR].[server_id]
 LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
 ON [sJOB].[category_id] = [sCAT].[category_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
 ON [sJOB].[job_id] = [sJSTP].[job_id]
 AND [sJOB].[start_step_id] = [sJSTP].[step_id]
 LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
 ON [sJOB].[owner_sid] = [sDBP].[sid]
 LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
 ON [sJOB].[job_id] = [sJOBSCH].[job_id]
 LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
 ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]

  • SQL Server Agent Job Steps Execution Information

by this Script we can find all info related to the Steps and the Execution

SELECT
 [sJOB].[job_id] AS [JobID]
 , [sJOB].[nameAS [JobName]
 , [sJSTP].[step_uid] AS [StepID]
 , [sJSTP].[step_id] AS [StepNo]
 , [sJSTP].[step_name] AS [StepName]
 CASE [sJSTP].[subsystem]
 WHEN 'ActiveScripting' THEN 'ActiveX Script'
 WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
 WHEN 'PowerShell' THEN 'PowerShell'
 WHEN 'Distribution' THEN 'Replication Distributor'
 WHEN 'Merge' THEN 'Replication Merge'
 WHEN 'QueueReader' THEN 'Replication Queue Reader'
 WHEN 'Snapshot' THEN 'Replication Snapshot'
 WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
 WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
 WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
 WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
 WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
 ELSE sJSTP.subsystem
 END AS [StepType]
 , [sPROX].[nameAS [RunAs]
 , [sJSTP].[database_name] AS [Database]
 , [sJSTP].[command] AS [ExecutableCommand]
 CASE [sJSTP].[on_success_action]
 WHEN THEN 'Quit the job reporting success'
 WHEN THEN 'Quit the job reporting failure'
 WHEN THEN 'Go to the next step'
 WHEN THEN 'Go to Step: '
 + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
 ' '
 + [sOSSTP].[step_name]
 END AS [OnSuccessAction]
 , [sJSTP].[retry_attempts] AS [RetryAttempts]
 , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
 CASE [sJSTP].[on_fail_action]
 WHEN THEN 'Quit the job reporting success'
 WHEN THEN 'Quit the job reporting failure'
 WHEN THEN 'Go to the next step'
 WHEN THEN 'Go to Step: '
 + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
 ' '
 + [sOFSTP].[step_name]
 END AS [OnFailureAction]
FROM
 [msdb].[dbo].[sysjobsteps] AS [sJSTP]
 INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
 ON [sJSTP].[job_id] = [sJOB].[job_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
 ON [sJSTP].[job_id] = [sOSSTP].[job_id]
 AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
 ON [sJSTP].[job_id] = [sOFSTP].[job_id]
 AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
 LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
 ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
ORDER BY [JobName], [StepNo]
  • Jobs Report by OnSuccessAction and on_fail_action
SELECT
CASE [sJSTP].[on_success_action]
 WHEN THEN 'Quit the job reporting success'
 WHEN THEN 'Quit the job reporting failure'
 WHEN THEN 'Go to the next step'
 WHEN THEN 'Go to Step: '
 + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
 ' '
 + [sOSSTP].[step_name]
 END AS [OnSuccessAction]
 CASE [sJSTP].[on_fail_action]
 WHEN THEN 'Quit the job reporting success'
 WHEN THEN 'Quit the job reporting failure'
 WHEN THEN 'Go to the next step'
 WHEN THEN 'Go to Step: '
 + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
 ' '
 + [sOFSTP].[step_name]
 END AS [OnFailureAction]
,[sJOB].[nameAS [JobName] ,
 [sJSTP].[step_id] AS [StepNo]
 , [sJSTP].[step_name] AS [StepName]
 CASE [sJSTP].[subsystem]
 WHEN 'ActiveScripting' THEN 'ActiveX Script'
 WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
 WHEN 'PowerShell' THEN 'PowerShell'
 WHEN 'Distribution' THEN 'Replication Distributor'
 WHEN 'Merge' THEN 'Replication Merge'
 WHEN 'QueueReader' THEN 'Replication Queue Reader'
 WHEN 'Snapshot' THEN 'Replication Snapshot'
 WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
 WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
 WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
 WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
 WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
 ELSE sJSTP.subsystem
 END AS [StepType]
FROM
 [msdb].[dbo].[sysjobsteps] AS [sJSTP]
 INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
 ON [sJSTP].[job_id] = [sJOB].[job_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
 ON [sJSTP].[job_id] = [sOSSTP].[job_id]
 AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
 ON [sJSTP].[job_id] = [sOFSTP].[job_id]
 AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
 LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
 ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
 --Where [sJSTP].[on_success_action] = 2
 Where [sJSTP].[on_fail_action] = 1
ORDER BY [JobName], [StepNo]
[/sq]</pre>
<ul>
          <li><strong>Check or Change job owner</strong></li>
</ul>
<pre>
IF you have 50 job on your DB Server and you need to change the Job owner oneby one it will take more time so we will do DMV to manage this issue 
Check the Owner Job:
1
SELECT
 sv.name AS [Name],
 sv.job_id AS [JobID],
 l.name AS UserName
 FROM
 msdb.dbo.sysjobs_view AS sv
 INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
 ORDER BY
 sv.[NameASC

After we check the job owner now we need to change this job owner by anther one

DECLARE @Jobname Nvarchar
DECLARE @NewOwner varchar(200)
DECLARE @OldName varchar(200)
SET @NewOwner = 'New User Name'
SET @OldName = 'sa'
SELECT
sv.name AS [Name],
sv.job_id AS [JobID],
l.name AS [OwnerName]
INTO #SQLJobs
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
WHERE l.name like @OldName
ORDER BY
sv.[NameASC
SELECT FROM #SQLJobs
WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN
 SELECT TOP 1 @JobID = JobID FROM #SQLJobs
 EXEC msdb.dbo.sp_update_job @job_id= @JobID,
 @owner_login_name=@NewOwner
 DELETE FROM #SQLJobs WHERE JobID = @JobID
END
DROP TABLE #SQLJobs
  • List by jobs are ruining now on your DB Server.
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN THEN 'Inactive'
WHEN THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
'-'
substring (x.last_run_date, 5, 2)
'-'
substring (x.last_run_date, 7, 2)
' '
substring (x.last_run_time, 1, 2)
':'
substring (x.last_run_time, 3, 2)
':'
substring (x.last_run_time, 5, 2)
'.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN THEN 'Fail'
WHEN THEN 'Success'
WHEN THEN 'Retry'
WHEN THEN 'Cancel'
WHEN THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
where x.running = 1
CREATE TABLE #enum_job (
 Job_ID UNIQUEIDENTIFIER
 ,Last_Run_Date INT
 ,Last_Run_Time INT
 ,Next_Run_Date INT
 ,Next_Run_Time INT
 ,Next_Run_Schedule_ID INT
 ,Requested_To_Run INT
 ,Request_Source INT
 ,Request_Source_ID VARCHAR(100)
 ,Running INT
 ,Current_Step INT
 ,Current_Retry_Attempt INT
 ,STATE INT
 )
INSERT INTO #enum_job
EXEC master.dbo.xp_sqlagent_enum_jobs 1
 ,garbage
SELECT R.NAME
 ,R.last_run_date
 ,R.RunningForTime
 ,GETDATE() AS now
FROM #enum_job a
INNER JOIN (
 SELECT j.NAME
 ,J.JOB_ID
 ,ja.run_requested_date AS last_run_date
 ,(DATEDIFF(mi, ja.run_requested_date, GETDATE())
 AS RunningFor
 ,CASE LEN(CONVERT(VARCHAR(5), DATEDIFF(MI, JA.
 RUN_REQUESTED_DATE, GETDATE()) / 60))
 WHEN 1
 THEN '0' CONVERT(VARCHAR(5), DATEDIFF(mi, ja.
 run_requested_date, GETDATE()) / 60
 )
 ELSE CONVERT(VARCHAR(5), DATEDIFF(mi, ja.
 run_requested_date, GETDATE()) / 60)
 END ':' CASE LEN(CONVERT(VARCHAR(5), (
 DATEDIFF(MI, JA.RUN_REQUESTED_DATE,
 GETDATE()) % 60
 )))
 WHEN 1
 THEN '0' CONVERT(VARCHAR(5), (
 DATEDIFF(mi, ja.
 run_requested_date, GETDATE()
 ) % 60
 ))
 ELSE CONVERT(VARCHAR(5), (
 DATEDIFF(mi, ja.run_requested_date,
 GETDATE()) % 60
 ))
 END ':' CASE LEN(CONVERT(VARCHAR(5), (
 DATEDIFF(SS, JA.RUN_REQUESTED_DATE,
 GETDATE()) % 60
 )))
 WHEN 1
 THEN '0' CONVERT(VARCHAR(5), (
 DATEDIFF(ss, ja.
 run_requested_date, GETDATE()
 ) % 60
 ))
 ELSE CONVERT(VARCHAR(5), (
 DATEDIFF(ss, ja.run_requested_date,
 GETDATE()) % 60
 ))
 END AS RunningForTime
 FROM msdb.dbo.sysjobactivity AS ja
 LEFT OUTER JOIN msdb.dbo.sysjobhistory AS jh ON ja.
 job_history_id = jh.instance_id
 INNER JOIN msdb.dbo.sysjobs_view AS ON ja.job_id = j.job_id
 WHERE (
 ja.session_id = (
 SELECT MAX(session_id) AS EXPR1
 FROM msdb.dbo.sysjobactivity
 )
 )
 ) R ON R.job_id = a.Job_Id
 AND a.Running = 1
DROP TABLE #enum_job
  • Jobs With Execution Long Time
DECLARE @HistoryStartDate datetime
 ,@HistoryEndDate datetime
 ,@MinHistExecutions int
 ,@MinAvgSecsDuration int
SET @HistoryStartDate = '19000101'
SET @HistoryEndDate = GETDATE()
SET @MinHistExecutions = 1.0
SET @MinAvgSecsDuration = 1.0
DECLARE @currently_running_jobs TABLE (
 job_id UNIQUEIDENTIFIER NOT NULL
 ,last_run_date INT NOT NULL
 ,last_run_time INT NOT NULL
 ,next_run_date INT NOT NULL
 ,next_run_time INT NOT NULL
 ,next_run_schedule_id INT NOT NULL
 ,requested_to_run INT NOT NULL
 ,request_source INT NOT NULL
 ,request_source_id SYSNAME NULL
 ,running INT NOT NULL
 ,current_step INT NOT NULL
 ,current_retry_attempt INT NOT NULL
 ,job_state INT NOT NULL
 )
--capture details on jobs
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
;WITH JobHistData AS
(
 SELECT job_id
 ,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
 ,secs_duration=run_duration/10000*3600
 +run_duration%10000/100*60
 +run_duration%100
 FROM msdb.dbo.sysjobhistory
 WHERE step_id = 0 --Job Outcome
 AND run_status = 1 --Succeeded
)
,JobHistStats AS
(
 SELECT job_id
 ,AvgDuration = AVG(secs_duration*1.)
 ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
 FROM JobHistData
 WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
 AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101'GROUP BY job_id HAVINGCOUNT(*) >= @MinHistExecutions
 AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)
SELECT jd.job_id
 ,j.name AS [JobName]
 ,MAX(act.start_execution_date) AS [ExecutionDate]
 ,AvgDuration AS [Historical Avg Duration (secs)]
 ,AvgPlus2StDev AS [Min Threshhold (secs)]
FROM JobHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL
WHERE secs_duration > AvgPlus2StDev
AND DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev
AND crj.job_state = 1
GROUP BY jd.job_id, j.name, AvgDuration, AvgPlus2StDev
  • Select Failed Job
select J.Name,JH.Run_Status,JH.Message,
convert(datetime, rtrim(JH.run_date))
+ ((JH.run_time/10000 * 3600)
+ ((JH.run_time%10000)/100*60)
+ (JH.run_time%10000)%100) / (86399.9964 ) as run_datetime
,* from msdb..sysjobs J inner join msdb..sysjobhistory JH
on J.Job_ID = JH.job_id
--Where J.Name = 'data_mart_tab4'
where JH.Run_Status = 0
  • How to search on your Jobs TEXT.

You can find my post ( Which Job Executed my Stored Procedures..? ) about this point

Now we Finished the Part of How to monitor and mange your SQL Server jobs ..?  after we Covered 20 Point with more than 20 Script 

Point Covered in the 2 posts of How to manage and Monitor SQL Server Jobs

  1. Check SQL Server jobs First Vision..?
  2. Check jobs Status ..? Is it enabled or Disabled ..?
  3. When was SQL agent job was created or when was last time it was modified..?
  4. Identify newly created SQL Server Agent Jobs
  5. Check enabled jobs Without notification setup ..?
  6. retrieving enabled operators in SQL Server Agent
  7. Update SQL Server Agent Jobs without any notification by New notification
  8. SQL Server Agent Job Setup and Configuration Information..
  9. Check jobs With Enabled Schedule ..
  10. Check SQL Server Agent Job Schedule Information.
  11. Configuring SQL Agent Jobs to Write to Windows Event Log.
  12. Generate SQL Agent Job Schedule Report
  13. SQL Server Agent Job Setup and Configuration Information
  14. SQL Server Agent Job Steps Execution Information
  15. Jobs Report by OnSuccessAction and on_fail_action
  16. Check or Change job owner
  17. List by jobs are ruining now on your DB Server.
  18. Jobs With Execution Long Time .
  19. Select Failed Job
  20. How to search on your Jobs TEXT.
Follow Me 
Mostafa Elmasry 
 

Tags: , ,

How to monitor your SQL Server instances..(3/10)?

in the previous Post i finished the Part of How to Diagnosis MSSQL Server or MSSQL Database with fast way..? but to be honest with you this really very big part and can’t Finished it coz this need much T-SQL , Professional  Third party tool like dynatrace really wonderful Tool don’t miss any corn. 

So today i will start new Part in our Series How to Administrate and Monitor SQL Server Jobs by T-SQL..?

So let’s go to Start

WHAT YOU NEED in SQL SERVER JOB WE COVERED HERE

First thing any DBA need to monitor the SQL Agent at the first HE/SHE need to know the First Vision in his/here SQL Server jobs

Check SQL Server jobs First Vision..?

SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
 'Enabled'=case
 WHEN a.enabled = 0 THEN 'No'
 WHEN a.enabled = 1 THEN 'Yes'
 end,
 substring(b.name,1,30) AS [Name of the schedule],
 'Frequency of the schedule execution'=case
 WHEN b.freq_type = 1 THEN 'Once'
 WHEN b.freq_type = 4 THEN 'Daily'
 WHEN b.freq_type = 8 THEN 'Weekly'
 WHEN b.freq_type = 16 THEN 'Monthly'
 WHEN b.freq_type = 32 THEN 'Monthly relative'
 WHEN b.freq_type = 32 THEN 'Execute when SQL Server Agent starts'
 END,
 'Units for the freq_subday_interval'=case
 WHEN b.freq_subday_type = 1 THEN 'At the specified time'
 WHEN b.freq_subday_type = 2 THEN 'Seconds'
 WHEN b.freq_subday_type = 4 THEN 'Minutes'
 WHEN b.freq_subday_type = 8 THEN 'Hours'
 END,
 cast(cast(b.active_start_date as varchar(15)) as datetime) asactive_start_date,
 cast(cast(b.active_end_date as varchar(15)) as datetime) as active_end_date,
 Stuff(Stuff(right('000000'+Cast(c.next_run_time asVarchar),6),3,0,':'),6,0,':'as Run_Time,
 convert(varchar(24),b.date_created) as Created_Date
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysJobschedules c ON a.job_id = c.job_id
INNER JOIN msdb.dbo.SysSchedules b on b.Schedule_id=c.Schedule_id
GO

This Script will return to you :

Job Name / Enabled or Disabled /  Name of the scheduled/  Frequency of the schedule execution / Units for the freq_subday_interval/ active_start_date/ active_end_date/ Run_Time /Created_Date.

From this Script we can do most Customization

Check jobs Status ..? Is it enabled or Disabled ..?

SELECT DISTINCT substring(a.name,1,100) AS [Job Name],
 'Job Enabled..?'=case
 WHEN a.enabled = 0 THEN 'No'
 WHEN a.enabled = 1 THEN 'Yes'
 end
FROM msdb.dbo.sysjobs a

When was a SQL Agent job was created or when was the last time it was modified?

if you need to know this info the the date a SQL Agent job was created or the last time it was modified, you can find the information in the sysjobs table in the MSDB database.

Select Name,
 Date_Created,
 Date_Modified
 From msdb..sysjobs

Identify newly created SQL Server Agent Jobs 

this Script will return the Jobs created in last 15 day

SELECT [Name], [Date_Created]
FROM MSDB.dbo.sysjobs
WHERE [Date_Created] BETWEEN DATEADD(dd, -15, GETDATE()) AND GETDATE();

Check enabled jobs Without notification setup ..?

Jobs nightmare Really from past Experience this very Important point IF your jobs is enabled notification or no ? sure it must be YES Enabled to be know what happened in your Jobs to don’t see nightmare.

SELECT [Name], [Date_Created], 'Job Enabled..?'=case
 WHEN enabled = 0 THEN 'No'
 WHEN enabled = 1 THEN 'Yes'
 end ,
 'notification Enabled..?'=case
 WHEN Notify_Level_Email = 0 THEN 'No'
 WHEN Notify_Level_Email = 1 THEN 'Yes'
 end
FROM MSDB.dbo.sysjobs
WHERE [Notify_Level_Email] = 0
AND [Enabled] = 1;

After this Script What you should do if you have some jobs without Notification setup at this time waste your time go direct to update this jobs by the Notification setup.so at the first you should know what the Operator you will pass in the Notification

retrieving enabled operators in SQL Server Agent 

SELECT [ID], [Name], [Enabled]
FROM MSDB.dbo.sysoperators
WHERE [Enabled] = 1
ORDER BY [Name];
GO

Now after you select one from sysoperators you should go yo update your Jobs

Update SQL Server Agent Jobs without any notification by New notification 

UPDATE S
SET S.[notify_level_email] = 2,
S.[notify_email_operator_id] = 1----Select the ID for your Operator from the Previous Query(retrieving enabled operators)
FROM MSDB.dbo.sysjobs S
WHERE S.[Notify_Level_Email] = 0
AND S.[Enabled] = 1;
GO

SQL Server Agent Job Setup and Configuration Information..

SELECT
 [sJOB].[job_id] AS [JobID]
 , [sJOB].[nameAS [JobName]
 , [sDBP].[nameAS [JobOwner]
 , [sCAT].[nameAS [JobCategory]
 , [sJOB].[description] AS [JobDescription]
 CASE [sJOB].[enabled]
 WHEN THEN 'Yes'
 WHEN THEN 'No'
 END AS [IsEnabled]
 , [sJOB].[date_created] AS [JobCreatedOn]
 , [sJOB].[date_modified] AS [JobLastModifiedOn]
 , [sSVR].[nameAS [OriginatingServerName]
 , [sJSTP].[step_id] AS [JobStartStepNo]
 , [sJSTP].[step_name] AS [JobStartStepName]
 CASE
 WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
 ELSE 'Yes'
 END AS [IsScheduled]
 , [sSCH].[schedule_uid] AS [JobScheduleID]
 , [sSCH].[nameAS [JobScheduleName]
 CASE [sJOB].[delete_level]
 WHEN THEN 'Never'
 WHEN THEN 'On Success'
 WHEN THEN 'On Failure'
 WHEN THEN 'On Completion'
 END AS [JobDeletionCriterion]
FROM
 [msdb].[dbo].[sysjobs] AS [sJOB]
 LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
 ON [sJOB].[originating_server_id] = [sSVR].[server_id]
 LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
 ON [sJOB].[category_id] = [sCAT].[category_id]
 LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
 ON [sJOB].[job_id] = [sJSTP].[job_id]
 AND [sJOB].[start_step_id] = [sJSTP].[step_id]
 LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
 ON [sJOB].[owner_sid] = [sDBP].[sid]
 LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
 ON [sJOB].[job_id] = [sJOBSCH].[job_id]
 LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
 ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
ORDER BY [JobName]

Check Jobs without Schedule ..

Select Name AS [No schedule in this Job]
 from [msdb]..[sysjobs] where job_id not in
 (Select job_id from MSDB..sysjobschedules )

Check jobs With Enabled Schedule ..

Select [sJOB].[nameAS [JobName],
Case
When [sSCH].[schedule_uid] IS Null THEN 'NO'
 ELSE 'Yes'
 End AS 'IS schedules Enabled..?'
 from
 [msdb].[dbo].[sysjobs] AS [sJOB]
 Inner Join [msdb].[dbo].[sysjobschedules] [sJOBSCH]
 On [sJOBSCH].job_id = [sJOB].job_id
 Inner Join [sysschedules] AS [sSCH]
 On [sSCH].schedule_id=[sJOBSCH].schedule_id

Now after we determined what is the jobs have Schedule and what is no Actually we need to know the Schedule Information for each job.

Check SQL Server Agent Job Schedule Information..

---SQL Server Agent Job Schedule Information
SELECT
 [schedule_uid] AS [ScheduleID]
 , [nameAS [ScheduleName]
 CASE [enabled]
 WHEN THEN 'Yes'
 WHEN THEN 'No'
 END AS [IsEnabled]
 CASE
 WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
 WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
 WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
 WHEN [freq_type] = 1 THEN 'One Time'
 END [ScheduleType]
 CASE [freq_type]
 WHEN THEN 'One Time'
 WHEN THEN 'Daily'
 WHEN THEN 'Weekly'
 WHEN 16 THEN 'Monthly'
 WHEN 32 THEN 'Monthly - Relative to Frequency Interval'
 WHEN 64 THEN 'Start automatically when SQL Server Agent starts'
 WHEN 128 THEN 'Start whenever the CPUs become idle'
 END [Occurrence]
 CASE [freq_type]
 WHEN THEN 'Occurs every ' CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
 WHEN THEN 'Occurs every ' CAST([freq_recurrence_factor] AS VARCHAR(3))
 ' week(s) on '
 CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END
 CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END
 CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END
 CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END
 CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END
 CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END
 CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END
 WHEN 16 THEN 'Occurs on Day ' CAST([freq_interval] AS VARCHAR(3))
 ' of every '
 CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)'
 WHEN 32 THEN 'Occurs on '
 CASE [freq_relative_interval]
 WHEN THEN 'First'
 WHEN THEN 'Second'
 WHEN THEN 'Third'
 WHEN THEN 'Fourth'
 WHEN 16 THEN 'Last'
 END
 ' '
 CASE [freq_interval]
 WHEN THEN 'Sunday'
 WHEN THEN 'Monday'
 WHEN THEN 'Tuesday'
 WHEN THEN 'Wednesday'
 WHEN THEN 'Thursday'
 WHEN THEN 'Friday'
 WHEN THEN 'Saturday'
 WHEN THEN 'Day'
 WHEN THEN 'Weekday'
 WHEN 10 THEN 'Weekend day'
 END
 ' of every ' CAST([freq_recurrence_factor] AS VARCHAR(3))
 ' month(s)'
 END AS [Recurrence]
 CASE [freq_subday_type]
 WHEN THEN 'Occurs once at '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN THEN 'Occurs every '
 CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 ' & '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_end_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN THEN 'Occurs every '
 CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 ' & '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_end_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN THEN 'Occurs every '
 CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 ' & '
 + STUFF(
 STUFF(RIGHT('000000' CAST([active_end_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 END [Frequency]
 , STUFF(
 STUFF(CAST([active_start_date] AS VARCHAR(8)), 5, 0, '-')
 , 8, 0, '-'AS [ScheduleUsageStartDate]
 , STUFF(
 STUFF(CAST([active_end_date] AS VARCHAR(8)), 5, 0, '-')
 , 8, 0, '-'AS [ScheduleUsageEndDate]
 , [date_created] AS [ScheduleCreatedOn]
 , [date_modified] AS [ScheduleLastModifiedOn]
FROM [msdb].[dbo].[sysschedules]
ORDER BY [ScheduleName]

this not the end :)  Do not go here and there Check your mobile notification daily check your email notification daily check our blog 

Follow Me to see my new posts and to complete with me this Amazing part How to Administrate and Monitor SQL Server Jobs by T-SQL..?