Monday 24 November 2014

SQL Server Agent Jobs History

SQL Server stores Agent job information and execution history in MSDB system tables. SSMS GUI provides a navigation to Jobs and history for all the jobs but sometimes that is not sufficient for deep analysis. The Job history viewer is not easy to get which jobs running longer, steps getting failed, do not have schedules and notification alerts.
Scripts in post will make analysis and auditing of SQL Server Agent Jobs many times easier.

List of currently running jobs and their Status
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Currently running jobs and their Status
 
SELECT  jb.name,
        jb.originating_server ServerName,
        ja.run_requested_date JobStartTime,
        DATEDIFF(MINUTE, ja.run_requested_date, GETDATE()) as TimeElapsed_Minutes,
        case when ja.last_executed_step_id is null
             then 'Step '+Cast (start_step_id as varchar(20)) +' ['+sstep.step_name+'] executing'
             else 'Step ' +Cast ( last_executed_step_id+1 as varchar(20))+' ['+ lstep.step_name
                  + '] executing' 
        end CurrentStepID , sstep.step_name JobStartedfromStep
        , Case ja.run_requested_source when 1 then 'SCHEDULER'
                               when 2 then 'ALERTER'
                               when 3 then 'BOOT'
                               when 4 then 'USER'
                               when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end JobRunSource
FROM    msdb.dbo.sysjobs_view jb
        INNER JOIN msdb.dbo.sysjobactivity ja ON jb.job_id = ja.job_id
        INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = ja.session_id
        LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id =start_step_id and sstep.job_id=jb.job_id
        LEFT JOIN MSDB.DBO.sysjobsteps lstep ON lstep.step_id =last_executed_step_id + 1 and lstep.job_id=jb.job_id 
WHERE   stop_execution_date IS NULL 
        AND run_requested_date IS NOT NULL

Execution time history for one month per Job in minutes
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Jobs execution time in minutes 

SELECT jb.name as 'JobName'
 , msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as JobRunDate
 , ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100 + 31 ) / 60) AS JobRunDuration_Minutes
 , Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus                           
FROM msdb.dbo.sysjobs jb 
INNER JOIN msdb.dbo.sysjobhistory jh  ON jb.job_id = jh.job_id 
WHERE step_id=0 -- '(Job outcome)'
AND DATEDIFF(MM, msdb.dbo.agent_datetime(jh.run_date, jh.run_time), GETDATE())=0 --- One Month
AND ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60
         + jh.run_duration%100 + 31 ) / 60)> 5 -- Greater than 5 mins 
AND jb.name='Job infinite loop'                                                            
ORDER BY JobName, JobRunDate DESC

Execution time history for one month per Job Step in minutes
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Job Steps execution time in minutes 

SELECT rank() over(ORDER BY jb.name,msdb.dbo.agent_datetime(jh.run_date, jh.run_time) desc) JobExecutionID
,jb.name as 'JobName'
, jstp.step_name as 'StepName'
, msdb.dbo.agent_datetime(jh.run_date, jh.run_time) as 'JobRunDate'
, ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60 + jh.run_duration%100 + 31 ) / 60) AS 'JobRunDurationMinutes'
, Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus                             
FROM msdb.dbo.sysjobs jb 
INNER JOIN msdb.dbo.sysjobsteps jstp ON jb.job_id = jstp.job_id
INNER JOIN msdb.dbo.sysjobhistory jh  ON jb.job_id = jh.job_id 
WHERE jh.step_id >0 -- '(Job outcome)'
AND DATEDIFF(MM, msdb.dbo.agent_datetime(jh.run_date, jh.run_time), GETDATE())=0 --- One Month
AND ((jh.run_duration/10000*3600 + (jh.run_duration/100)%100*60                                    
        + jh.run_duration%100 + 31 ) / 60)> 5 -- Greater than 5 mins
AND jb.name='Job infinite loop'
ORDER BY JobName, JobRunDate DESC

Get job history last executed by whom
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Get job history last executed by whom  

SELECT case jb.[enabled] when 1 then 'Active' else 'Inactive' end JobCurrentStatus
, jb.name JobName , ja.start_execution_date LastExecutionDate
, Case ja.run_requested_source when 1 then 'SCHEDULER'
                               when 2 then 'ALERTER'
                               when 3 then 'BOOT'
                               when 4 then 'USER'
                               when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end 'JobRunSource'
,Case jh.run_status when 0 then 'Failed'
                              when 1 then 'Succeeded'
                              when 2 then 'Retry'
                              when 3 then 'Stopped' else 'Unknown' end  JobRunStatus

FROM msdb.dbo.sysjobs AS jb
INNER JOIN msdb.dbo.sysjobactivity AS ja ON ja.job_id = jb.job_id
INNER JOIN msdb.dbo.sysjobhistory jh on jh.instance_id=ja.job_history_id
WHERE exists (  SELECT sja.job_id,MAX(sja.start_execution_date)
                FROM msdb.dbo.sysjobactivity sja
                WHERE start_execution_date IS NOT NULL
                AND sja.job_id=ja.job_id
                AND sja.start_execution_date=ja.start_execution_date
                group by sja.job_id
              )
ORDER BY ja.start_execution_date DESC

Get jobs list by notification alter operators
--- Script By: Amna Asif___________________For: sqlcache.blogspot.com
--- Purpose: Get jobs list by notification alter operators 

SELECT jb.name JobName, 
CASE notify_level_email WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'Whenever the job completes (regardless of the job outcome)' 
ELSE 'Unknown' END  'NotificationEvent'
, isnull(op.name ,'') OperatorName
, Case WHEN op.name is not null THEN Case op.[enabled] when 1 then 'Active' else 'Inactive' end  ELSE '' END 
FROM msdb.dbo.sysjobs jb left join msdb.dbo.sysoperators op
on jb.notify_email_operator_id=op.id
ORDER BY isnull(op.name ,''), jb.name 

Saturday 22 November 2014

SQL Server Database Mail

Database Mail is a great feature for sending email messages and notification alerts from SQL Server Database Engine. Once you configured SMTP settings, database applications can send email messages which may contain query results, include files and notification alerts. Database Mail uses msdb service broker queues to send email messages and logs email activity to msdb database tables.

There are two ways to configure Database Mail. I prefer T-SQL because we run the same setup on multiple servers.
  1. Transact-SQL
  2. Database Mail Configuration Wizard
Example in post shows to configure SQL Server Database Mail using Gmail Account.

Method 1: Transact-SQL
  • Enable Database Mail
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
  • Create Database Mail Account
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create Database Mail Account

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA',
@email_address = sqlcache@gmail.com', -- change valid email address
@display_name = 'SQL Cache DBA Mail',
@description = 'Database Mail Account',
@mailserver_name = 'smtp.gmail.com' ,
@mailserver_type =  'SMTP' ,
@port =  587 ,
@username =  'sqlcache@gmail.com',  -- change valid email address
@password =  'password',   -- email password
@use_default_credentials=0,
@enable_ssl=1
Parameter
Description
@use_default_credentials
When this value 1, Database Mail uses the credentials of Database Engine.

 When this value 0, Database Mail uses the @username and @password provided with Account creation for authentication on the SMTP server.
@enable_ssl
if SSL is required on your SMTP server. enable_ssl is bit
  • Create Database Mail profile
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Profile',
@description = 'SQL Cache DBA Profile' ; 
  • Add the account to the profile
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Profile',
@account_name = 'DBA',
@sequence_number =1 ; --- check this
  • Grant access to the profile to all users
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Grant access to the profile to all users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DBA Profile',
@principal_name = 'public', 
@is_default = 1 ; 
  • Verify DBA profile and accounts
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Verify DBA profile and accounts

SELECT * FROM msdb.dbo.sysmail_account  WHERE  name like '%DBA%'
SELECT * FROM msdb.dbo.sysmail_profile WHERE  name like '%DBA%'
  • Enable less secure apps option on link to receive emails on Gmail account.
  • Send test email to verify setup is configured correctly
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Send test email

EXEC msdb.dbo.sp_send_dbmail 
@profile_name='DBA Profile',
@recipients='sqlcache@gmail.com',
@subject='Test message',
@body='This is the body of the test message.'
  • Verify status of the email message
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Check email message status

--Status of the message 
SELECT last_mod_date, case sent_status 
when 1 then 'Sent Successfully' 
when 2 then 'Failed' 
when 3 then 'Unsent' end sent_status ,[subject] ,recipients 
FROM msdb.dbo.sysmail_mailitems 
ORDER BY last_mod_date DESC

--Reason of the message failure 
SELECT top 1  profile_id,items.last_mod_date,items.[subject],l.[description]  ErrorMessage,items.sent_status,recipients
FROM msdb.dbo.sysmail_faileditems as items 
INNER JOIN msdb.dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_id
ORER BY last_mod_date DESC
  • Update the information of existing Database Mail account if required. In my case I entered wrong password while account creation.
--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Update existing Database Mail account 

EXECUTE msdb.dbo.sysmail_update_account_sp 
@account_id = 5, --- ID of DBA account
@account_name = null,
@description = null, 
@email_address = null,
@display_name = null, 
@replyto_address = NULL, 
@mailserver_name = null,
@mailserver_type = null, 
@port = null, 
@timeout = null,
@username =  'sqlcache@gmail.com', --change valid email account 
@password =  'emailpassword', -- email password
@use_default_credentials = 0, 
@enable_ssl = 1 ; 
  • Database Mail can also be configured using Hotmail, Yahoo and AOL SMTP and account details.
SMTP Servers
Media
SMTP Mail Server Name
Port
Gmail
smtp.gmail.com
587
Hotmail
smtp.live.com
587
AOL
smtp.aol.com
587
Yahoo
smpt.mail.yahoo.com
25


Method 2:  D
atabase Mail Configuration Wizard 


















Reference link:
DatabaseMail Messaging Objects

Sunday 9 November 2014

Predicate effect in ON and WHERE clause

There is always a question, Is using predicate in ON clause gives the same result using predicate in Where clause of a query?
Actually it depends on the order in which the conditions of a query are evaluated logically in SQL Server; but if the query is an inner join, then there is no difference between using predicate as part of ON clause or Where. They might generate different result when query is with OUTER joins because the predicates in ON clause applied to the table before the join, whereas WHERE clause is applied to the result of the join.

SQL Server Logical Query Execution Order:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

ON clause predicate with Outer Join:

In case of OUTER JOIN query will follow sequence
SELECT  *
FROM    dbo.Employee emp
        LEFT JOIN dbo.EmployeeStatus es ON emp.Status_ID = es.Status_ID
                                           AND emp.Status_ID <> 2
1.       Table Employee scanning ( Table at left side of the Outer clause )
2.       Table EmployeeStatus scanning with predicate Status_ID<>2


Result Set:

WHERE clause predicate with Outer Join:

In Case of WHERE clause query will follow the sequence
SELECT  *
FROM    dbo.Employee emp
        LEFT JOIN dbo.EmployeeStatus es ON emp.Status_ID = es.Status_ID
WHERE   emp.Status_ID <> 2
1.       Table Employee scanning
2.       Table EmployeeStatus scanning
3.       Result set filtering based on predicate Status_ID<>2


Result Set :

Reference Link:
http://msdn.microsoft.com/en-us/library/ms177634.aspx

Sunday 19 October 2014

RTF to HTML Conversion

Converting document files is never easier, I recently had requirement to convert some RTF text into Simple HTML using SQL Server.

SQL Server RTF to HTML Conversion Code Limitations:
      • Color tags are not handled
      • Only few List item tags handled
      • Some of the tags are marked as not necessary and replaced with nothing 

Script is for RTF to HTML converter

Sunday 7 September 2014

SQL Server Import\ Export Image\Document Files

Documents storing inside database might be a requirement of SQL Server driven application. SQL Server uses nvarchar for unicode texts whereas varbinary data type for storing document files (like images, word documents, pdfs) in database.
Suppose you have a folder containing various document files (like images, word documents, pdfs, html) and it is required to perform certain actions to upload data from file system in to the database.

1.        List all files with their path in a folder and sub-folders to a Table
--------------------------------------------------------------------- 
CREATE PROCEDURE GetAllDirectoryFilesandFolders -- GetAllDirectoryFilesandFolders @RootDir='E:\Post\SqlCache'
(
@RootDir VARCHAR(1000)='E:\SQLCache'
)
AS
BEGIN

DECLARE  @RowIndex  INT
,        @SearchDir VARCHAR(1000)
  

IF EXISTS ( SELECT 1 FROM SQLCacheDocuments.SYS.TABLES WHERE name ='DocumentsRepository' ) 
DROP TABLE SQLCacheDocuments.dbo.DocumentsRepository


CREATE TABLE SQLCacheDocuments.dbo.DocumentsRepository
(
  FileID INT IDENTITY(1,1),
  DocumentFilePath VARCHAR(max),
  isfile INT,
  DocumentFileName VARCHAR(max),
  FileDepth INT
)

--- Get Root Direcotry Files\Folders
INSERT INTO DocumentsRepository(DocumentFileName, FileDepth,isFile)
EXEC MASTER.SYS.xp_dirtree @RootDir,1,1



--- If No file\Folder exits then exit 
IF NOT exists( SELECT * FROM DocumentsRepository )
RETURN


--- Update File\Folder Path
UPDATE DocumentsRepository
SET DocumentFilePath = @RootDir + '\' + DocumentFileName


--- Next root directory
SELECT @RowIndex=min(FileID) FROM DocumentsRepository


--- Get Sub Direcotry Files\Folders
WHILE 1=1
BEGIN

   SELECT @SearchDir = DocumentFilePath
   FROM DocumentsRepository
   WHERE FileID=@RowIndex

   INSERT INTO DocumentsRepository(DocumentFileName, FileDepth,isfile)
   EXEC MASTER.SYS.xp_dirtree @SearchDir,1,1

   UPDATE DocumentsRepository
   SET DocumentFilePath = @SearchDir + '\' + DocumentFileName
   WHERE DocumentFilePath is null

   SET @RowIndex = @RowIndex + 1

   -- Exit if current Rowindex is Max FileID
   IF ( @RowIndex > (SELECT max(FileID) FROM DocumentsRepository) or @RowIndex is null )
   BREAK

END


SELECT SUM(CASE ISFILE WHEN 0 THEN 1 ELSE 0 END )  TolalFolders
,      SUM(CASE ISFILE WHEN 1 THEN 1 ELSE 0 END )  TolalFiles
FROM  SQLCacheDocuments.dbo.DocumentsRepository


Delete
FROM  SQLCacheDocuments.dbo.DocumentsRepository
WHERE isfile=0


SELECT FileID, DocumentFilePath, DocumentFileName
FROM SQLCacheDocuments.dbo.DocumentsRepository
ORDER BY FileID

END
--------------------------------------------------------------------- 

2.       Divide files into two Tables based on operations
                                                   i.      Files (like Images, PDFs, Word documents ) which are inserted as binary
                                                 ii.       Files (text, HTML) whose content is inserted into the database
---------------------------------------------------------------------
SELECT * INTO SQLCacheDocuments.dbo.TextDocuments
FROM SQLCacheDocuments.dbo.DocumentsRepository
WHERE DocumentFileName like '%.html'
  OR  DocumentFileName like '%.TXT'
  
Alter table SQLCacheDocuments.dbo.TextDocuments ADD FileText NVARCHAR(max)

---------------------------------------

SELECT * INTO SQLCacheDocuments.dbo.HandDocuments
FROM SQLCacheDocuments.dbo.DocumentsRepository
WHERE DocumentFileName not like '%.html'
 AND  DocumentFileName not like '%.TXT'

ALTER TABLE SQLCacheDocuments.dbo.HandDocuments ADD FileBinary VARBINARY(max) 
---------------------------------------------------------------------

3. Import files (like Images, PDFs, Word documents ) as binary into the database table
--------------------------------------------------------------------- 
;WITH DocumentFiles
AS
(
SELECT FileID, DocumentFilePath
FROM SQLCacheDocuments.dbo.HandDocuments
WHERE FileBinary IS NULL
)
SELECT ' UPDATE SQLCacheDocuments.dbo.HandDocuments
                                 SET FileBinary= tempImg.BULKCOLUMN
                                  FROM OPENROWSET(BULK N''' + DocumentFilePath + ''', SINGLE_BLOB) as tempImg, SQLCacheDocuments.dbo.HandDocuments FileBinary
                                  WHERE CONVERT(int,FileBinary.FileID)='+ CONVERT(VARCHAR(200),FileID)+' GO '
FROM DocumentFiles
ORDER BY FileID
---------------------------------------------------------------------

4. Import content of files as text into the database table
--------------------------------------------------------------------- 
;WITH DocumentFiles
AS
(
SELECT FileID, DocumentFilePath
FROM SQLCacheDocuments.dbo.TextDocuments
WHERE FileText IS NULL
)
SELECT ' UPDATE SQLCacheDocuments.dbo.TextDocuments
                                 SET FileText= tempImg.BULKCOLUMN
                                  FROM OPENROWSET(BULK N''' + DocumentFilePath + ''', SINGLE_CLOB) as tempImg, SQLCacheDocuments.dbo.TextDocuments TextNote
                                  WHERE CONVERT(int,TextNote.FileID)='+ CONVERT(VARCHAR(200),FileID)+' GO '
FROM DocumentFiles
ORDER BY FileID
---------------------------------------------------------------------

5. Verify files binary by converting back to OS file
---------------------------------------------------------------------
;WITH DocumentFiles
AS
(
SELECT FileID, DocumentFileName
FROM SQLCacheDocuments.dbo.HandDocuments 
WHERE FILEID=1
)
SELECT ' EXEC master..xp_cmdshell ''BCP "SELECT FileBinary FROM SQLCacheDocuments.dbo. HandDocuments WHERE FileID ='
                        +Cast(FILEID as varchar(50))+'"  queryout "E:\Post\'+DocumentFileName
                        +'" -T -N -SsqlserverR2'''
FROM DocumentFiles
ORDER BY FileID
---------------------------------------------------------------------


6. Validate content of files by comparing OS file text and column value of database table.

7. Check files size and length of documents 
---------------------------------------------------------------------
SELECT (CAST(DATALENGTH(FileBinary)AS BIGINT) /1024.00) FileSize,* FROM SQLCacheDocuments.dbo.HandDocuments         
ORDER BY CAST(DATALENGTH(FileBinary) AS BIGINT) DESC       
        
SELECT Len(FileText),* FROM SQLCacheDocuments.dbo.TextDocuments
ORDER BY CAST(Len(FileText) AS BIGINT) DESC 
---------------------------------------------------------------------