Mustafa EL-Masry SQl server Administration

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.name, CAST(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].[name] AS [JobName]
 , [sDBP].[name] AS [JobOwner]
 , [sCAT].[name] AS [JobCategory]
 , [sJOB].[description] AS [JobDescription]
 , CASE [sJOB].[enabled]
 WHEN 1 THEN 'Yes'
 WHEN 0 THEN 'No'
 END AS [IsEnabled]
 , [sJOB].[date_created] AS [JobCreatedOn]
 , [sJOB].[date_modified] AS [JobLastModifiedOn]
 , [sSVR].[name] AS [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].[name] AS [JobScheduleName]
 , CASE [sJOB].[delete_level]
 WHEN 0 THEN 'Never'
 WHEN 1 THEN 'On Success'
 WHEN 2 THEN 'On Failure'
 WHEN 3 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].[name] AS [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].[name] AS [RunAs]
 , [sJSTP].[database_name] AS [Database]
 , [sJSTP].[command] AS [ExecutableCommand]
 , CASE [sJSTP].[on_success_action]
 WHEN 1 THEN 'Quit the job reporting success'
 WHEN 2 THEN 'Quit the job reporting failure'
 WHEN 3 THEN 'Go to the next step'
 WHEN 4 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 1 THEN 'Quit the job reporting success'
 WHEN 2 THEN 'Quit the job reporting failure'
 WHEN 3 THEN 'Go to the next step'
 WHEN 4 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 1 THEN 'Quit the job reporting success'
 WHEN 2 THEN 'Quit the job reporting failure'
 WHEN 3 THEN 'Go to the next step'
 WHEN 4 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 1 THEN 'Quit the job reporting success'
 WHEN 2 THEN 'Quit the job reporting failure'
 WHEN 3 THEN 'Go to the next step'
 WHEN 4 THEN 'Go to Step: '
 + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
 + ' '
 + [sOFSTP].[step_name]
 END AS [OnFailureAction]
,[sJOB].[name] AS [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.[Name] ASC

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.[Name] ASC
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 2 THEN 'Inactive'
WHEN 4 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 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 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 j 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
Mustafa EL-Masry SQl server Administration

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].[name] AS [JobName]
 , [sDBP].[name] AS [JobOwner]
 , [sCAT].[name] AS [JobCategory]
 , [sJOB].[description] AS [JobDescription]
 , CASE [sJOB].[enabled]
 WHEN 1 THEN 'Yes'
 WHEN 0 THEN 'No'
 END AS [IsEnabled]
 , [sJOB].[date_created] AS [JobCreatedOn]
 , [sJOB].[date_modified] AS [JobLastModifiedOn]
 , [sSVR].[name] AS [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].[name] AS [JobScheduleName]
 , CASE [sJOB].[delete_level]
 WHEN 0 THEN 'Never'
 WHEN 1 THEN 'On Success'
 WHEN 2 THEN 'On Failure'
 WHEN 3 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].[name] AS [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]
 , [name] AS [ScheduleName]
 , CASE [enabled]
 WHEN 1 THEN 'Yes'
 WHEN 0 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 1 THEN 'One Time'
 WHEN 4 THEN 'Daily'
 WHEN 8 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 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
 WHEN 8 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 1 THEN 'First'
 WHEN 2 THEN 'Second'
 WHEN 4 THEN 'Third'
 WHEN 8 THEN 'Fourth'
 WHEN 16 THEN 'Last'
 END
 + ' '
 + CASE [freq_interval]
 WHEN 1 THEN 'Sunday'
 WHEN 2 THEN 'Monday'
 WHEN 3 THEN 'Tuesday'
 WHEN 4 THEN 'Wednesday'
 WHEN 5 THEN 'Thursday'
 WHEN 6 THEN 'Friday'
 WHEN 7 THEN 'Saturday'
 WHEN 8 THEN 'Day'
 WHEN 9 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 1 THEN 'Occurs once at '
 + STUFF(
 STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6)
 , 3, 0, ':')
 , 6, 0, ':')
 WHEN 2 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 4 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 8 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..?

Mustafa EL-Masry Administration, SQl server Administration

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


in the previous post we Speaking about our idea for this Series of posts and i Started my First post with How to Diagnosis MSSQL Server or MSSQL Database with fast way ..? Today i will complete this part from How to Diagnosis  Your Database,

Database Information : in this Script we can cover this below points

  • How many databases are on the instance?
  • What recovery models are they using?
  • What is the log reuse wait description?
  • How full are the transaction logs ?
  • What compatibility level are they on?
  • What is the Page Verify Option?
  • Make sure auto_shrink and auto_close are not enabled!

SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS [Log Used %],
db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on, db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
db.is_auto_close_on, db.is_auto_shrink_on, db.target_recovery_time_in_seconds
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N’Log File(s) Used Size (KB)%’
AND ls.counter_name LIKE N’Log File(s) Size (KB)%’
AND ls.cntr_value > 0 OPTION (RECOMPILE);

Check Database File and all user database paths :

SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

How to Check the VLF count for All Databases:

CREATE TABLE #VLFInfo (RecoveryUnitID int, FileID int,
FileSize bigint, StartOffset bigint,
FSeqNo bigint, [Status] bigint,
Parity bigint, CreateLSN numeric(38));

CREATE TABLE #VLFCountResults(DatabaseName sysname, VLFCount int);

EXEC sp_MSforeachdb N’Use [?];

INSERT INTO #VLFInfo
EXEC sp_executesql N”DBCC LOGINFO([?])”;

INSERT INTO #VLFCountResults
SELECT DB_NAME(), COUNT(*)
FROM #VLFInfo;

TRUNCATE TABLE #VLFInfo;’

SELECT DatabaseName, VLFCount
FROM #VLFCountResults
ORDER BY VLFCount DESC;

DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;

 

How to get CPU utilization by database :

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N’dbid’) AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 — system databases
AND DatabaseID <> 32767 — ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

Login_Name list with the Session Count:

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

Check Adhoc Query:

SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N’Compiled Plan’
AND cp.objtype = N’Adhoc’
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE)

Top Cash quires by Execution count :

SELECT TOP (250) qs.execution_count, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time,
total_worker_time, total_logical_reads, 
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
			THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
	  ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS query_text 
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC OPTION (RECOMPILE);

this not the end i have lot of Scripts can help us to figure the most repeated issue in SQL Server so if you need to know the more Follow me in the Next Post

 


		


		
			
 





	

		

					

			 Mustafa EL-Masry SQl server Administration					


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

		

			

Any DBA or DB Analyst need to know two important point


How to administrate DBs in a single click..?


How to monitor your SQL Server instances..?


this from my opinion first point  How to administrate DBs in a single click..? we covered the most important subject in this point in this Series posted by SHEHAP EL-NAGAR :


How to administrate your databases in a single click..?! (1/8)


How to administrate your databases in a single click..?! (2/8)


How to administrate your databases in a single click..?! (3/8)


How to administrate your databases in a single click..?! (4/8)


How to administrate your databases in a single click..?! (5/8)


How to administrate your databases in a single click ..?! (6/8)


Follow up  SHEHAP EL-NAGAR to complete this wonderful and amazing series thanks for AMRO SELIM for his Constructive and fruitful collaboration in this Series .


but this not the end because we will start very Wonderful and Impressive Series to complete our plan to help any DBA or DB Analyst in his/here job now you can mange your database  and administrate it but How to monitor your SQL Server instances..?


1- How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?


2- How to Administrate and Monitor SQL Server Agent by T-SQL..?


3- How to Administrate and Monitor SQL Server Backup and Restore by T-SQL..?


4- How to Audit your SQL Server instance and database ..?


5-How To Monitor your instance and Database by Using ( DMV , Alerts , UCP Moitor , Third party tools..Etc)..?


and most of Question and topic i will cover it in this series to be repository for any DBA to Administrate and monitor his/here SQL Sever instance and Databases it will be DBA portfolio. Let’s Start


How to Diagnosis MSSQL Server or MSSQL Database with fast way ..?


At the first the goal from this post is making repository for scripts can help any DBA or DB Analyst to do Diagnoses on any MSSQL instance or Database by fast and easy way I Collected it after more Search and from the past Experience so if anyone has good script or idea can share it with us and we will share it by his Name


First thing instance level:


SQL and OS Version information :












SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [MSSQL OS info];












Installation Data for MSSQL:












SELECT @@SERVERNAME AS [Server Name], createdate AS [SQL Server Install Date]














FROM sys.syslogins














WHERE [sid] = 0x010100000000000512000000;












Server properties:












SELECT SERVERPROPERTY('MachineName') AS [MachineName],














SERVERPROPERTY('ServerName') AS [ServerName],














SERVERPROPERTY('InstanceName') AS [Instance],














SERVERPROPERTY('IsClustered') AS [IsClustered],














SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS[ComputerNamePhysicalNetBIOS],














SERVERPROPERTY('Edition') AS [Edition],














SERVERPROPERTY('ProductLevel') AS [ProductLevel],














SERVERPROPERTY('ProductVersion') AS [ProductVersion],














SERVERPROPERTY('ProcessID') AS [ProcessID],














SERVERPROPERTY('Collation') AS [Collation],














SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],














SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],














SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],














SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus];












SQL Server Services information :












SELECT servicename, startup_type_desc, status_desc,














last_startup_time, service_account, is_clustered, cluster_nodename














FROM sys.dm_server_services OPTION (RECOMPILE);












Hardware information for SQL Server Instance:












SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],














cpu_count/hyperthread_ratio AS [Physical CPU Count],














physical_memory_kb/1024 AS [Physical Memory (MB)],














 committed_target_kb/1024 AS [Committed Target Memory (MB)],














max_workers_count AS [Max Workers Count],














affinity_type_desc AS [Affinity Type],














sqlserver_start_time AS [SQL Server Start Time],














virtual_machine_type_desc AS [Virtual Machine Type]














FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);












Where the SQL Server Error log is located












SELECT is_enabled, [path], max_size, max_files














FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION(RECOMPILE);












Information about OS Cluster :












</pre>














SELECT VerboseLogging, SqlDumperDumpFlags, SqlDumperDumpPath,














 SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout














FROM sys.dm_os_cluster_properties WITH (NOLOCK) OPTION (RECOMPILE);












Instance Configuration Value:












SELECT name, value, value_in_use, [description]














FROM sys.configurations WITH (NOLOCK)














ORDER BY name OPTION (RECOMPILE);












I am not the owner for this Scripts only i do wide Search on the internet to Collect it Follow me in the Next post to know more Scripts About how we can Diagnose MSSQL Instance|Database.

		


		
			
 





	

		

					

			 Mustafa EL-Masry SQl server Administration					


Performance Dashboard Reports in SQL Server 2008
		

		

			

introduction :


Microsoft is Great Company Really i love this Company .


Microsoft have Tools Report name (SQL Server 2005 performance Dashboard Reports) it,s good Report From Microsoft you can Download the Tool and Customize it to Show in your Server Stander Reports .


The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server. The SQL Server instance being monitored and the Management Studio client used to run the reports must both be running SP2 or later.


Now i need to Setup This report on SQL Server 2008R2 so i will Explain it step by step.


    

  1. Download the Tool from website .
    2. 

  2. Install Performance Dashboard in the Default Location if you need to change it no problem but don’t forget the Path.
    3. 

  3. I installed this tool in the default path After Installation Go to this Path (C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard)
    4. 

  4. open your SQL Server You need to Effect This Report on .
    5. 

  5. Search on this Folder (PerformanceDashboard) to (Setup. Exe)
    6. 



Performance Dashborder_2


    

  1. By CTRL+F try to found this Statement ( select @ts_now = cpu_ticks) in your Code you will See this Line in this Script it .select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
    2. 

  2. Replace it by This T-SQL (select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info)
    3. 

  3. Performance Dashborder
    4. 

  4. Run this Script on your SQL Server after you replaced the code with new line if you try to run this Script on SQL Server 2005 it will give you Error :Msg 207, Level 16, State 1, Procedure usp_Main_GetCPUHistory, Line 6 Invalid column name ‘cpu_ticks_in_ms’.
    5. 

  5. Right Click on your SQL Server >> Reports >> Custom >> go to this Path (C:\Program Files (x86)\Microsoft SQL Server\90\Tools\PerformanceDashboard) >> Select (performance_dashboard_main.rdl) >> RUN
    6. 

  6. After this Step you will find your New report (Performance Dashboard ) is Working immediately .
    7. 

  7. If you need to Work it Again Right Click on your Server >> reports >>Performance_dashboard_main.
    8. 

  8. Performance_dashboard3
    9. 



Performance_dashboard_main


Performance_dashboard4


Performance_dashboardPerformance_dashboard5


 


in the finale i think it’s great report also if it not get All what we need but it’s good from SQL Server it’s have a good Interface to help all guys working on SQL Server Product .