DBCC MEMORYSTATUS
Return a lot of data about the memory st5atus in the SQL Server.
Get details of all of the queries here:
Get all constraints of a specific type
select *
from information_schema.table_
where CONSTRAINT_TYPE = 'UNIQUE'
-- 'CHECK' , 'UNIQUE' , 'PRIMARY KEY' , 'FOREIGN KEY'
Get Constraints details include columns
select t.TABLE_SCHEMA, t.TABLE_NAME,
t.CONSTRAINT_SCHEMA, t.CONSTRAINT_NAME, t.CONSTRAINT_TYPE,
c.COLUMN_NAME
from information_schema.table_
join information_schema.constraint_column_usage c
on t.Constraint_Name = c.Constraint_Name
--where t.CONSTRAINT_NAME = 'UC_Something'
--where t.CONSTRAINT_TYPE = 'Unique'
--where t.TABLE_NAME = 'TableName'
...
Get jobs that run more then usually (SQL Server)
DECLARE
@MinDelayPercent money = 0.5, -- run X% mare then the average duration
@MinDelaySeconds int = 300 -- minimum delay time =- in order to ignore short jobs delay
-- Get Running Jobs
CREATE TABLE #RunningJobs
(
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 #RunningJobs EXEC master.dbo.xp_sqlagent_enum_
; with avgDrtn as
(
select h.job_id,
avg ( ( (h.run_duration % 100) -- Seconds
+ 60 * ((h.run_duration /100) % 100) -- + minutes to seconds
+ 3600 * ((h.run_duration / 10000) % 100) -- + Hours to seconds
)
) as AvgJobDuration_Seconds
from msdb.dbo.sysjobhistory h
where h.step_id = 0
group by h.job_id
)
SELECT r.Job_ID, j.name AS JobName,
msdb.dbo.agent_datetime(r.Next
DATEDIFF(second, msdb.dbo.agent_datetime(r.Next
a.AvgJobDuration_Seconds
FROM #RunningJobs r
JOIN msdb.dbo.sysjobs j ON r.Job_ID=j.job_id
JOIN avgDrtn a ON j.job_id = a.job_id
WHERE -- job is still running
r.Running=1
AND -- ignore short jobs delay
( cast(DATEDIFF(second, msdb.dbo.agent_datetime(r.Next
-
cast(a.AvgJobDuration_Seconds as money)
) > @MinDelaySeconds
AND -- X% mare then the average duration
( cast(DATEDIFF(second, msdb.dbo.agent_datetime(r.Next
/
cast(a.AvgJobDuration_Seconds as money)
) > (1+@MinDelayPercent)
--ORDER BY name,next_run_date,next_run_
DROP TABLE #RunningJobs
Get dcheduled jobs and their schedules (SQL Server)
select j.job_id, j.name as JobName, j.[enabled] as JobEnabled,
js.schedule_id,
( CASE s.freq_type
WHEN 1 THEN 'One time only'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
WHEN 128 THEN 'When the computer is idle'
ELSE ''
END
) Frequently,
( CASE len(s.active_start_time)
WHEN 1 THEN cast('00:00:0' + cast(s.active_start_time as char) as char (8))
WHEN 2 THEN cast('00:00:' + cast(s.active_start_time as char) as char (8))
WHEN 3 THEN cast('00:0' + Left(right(s.active_start_time
WHEN 4 THEN cast('00:' + Left(right(s.active_start_time
WHEN 5 THEN cast('0' + Left(right(s.active_start_time
WHEN 6 THEN cast(Left(right(s.active_
END
) as RunTime
from msdb.dbo.sysjobs j
join msdb.dbo.sysjobschedules js on j.job_id = js.job_id
join msdb.dbo.sysschedules s on js.schedule_id = s.schedule_id
where s.[enabled] = 1
and s.freq_type between 4 and 32
order by s.freq_type, s.active_start_time
This query return the scheduling's of jobs in the server.
Jobs without scheduling won't be return (unless you change the query to LEFT JOINs) – in order to get the scheduling mapping in the server.
Average SQL jobs duration
select h.job_id,
avg (( (h.run_duration % 100) -- Seconds
+ 60 * ((h.run_duration /100) % 100) -- + minutes to seconds
+ 3600 * ((h.run_duration / 10000) % 100) -- + Hours to seconds
)
) as AvgJobDuration_Seconds
from msdb.dbo.sysjobhistory h
where h.step_id = 0
group by h.job_id
Current running SQL jobs
IF OBJECT_ID('tempdb.dbo.#
DROP TABLE #RunningJobs
CREATE TABLE #RunningJobs
(
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 #RunningJobs
EXEC master.dbo.xp_sqlagent_enum_
SELECT j.job_id, j.name AS JobName,
(CASE WHEN next_run_date=0 THEN '[Not scheduled]' ELSE CONVERT(VARCHAR,msdb.dbo.agent
FROM #RunningJobs r
JOIN msdb.dbo.sysjobs j on r.Job_ID = j.job_id
WHERE r.Running = 1 -- running now
ORDER BY j.name, r.next_run_date, r.next_run_time
IF OBJECT_ID('tempdb.dbo.#
DROP TABLE #RunningJobs
An item with the same key has already been added
Error when try to open an alert properties:
An item with the same key has already been added. (mscorlib)
Cause: There are 2 (or more) jobs with the same name.
Find the duplicated names of jobs using this query:
select name, count(*) from msdb.dbo.sysjobs
group by name having count(*) > 1
Fix: Rename or delete one of the jobs with the duplicate name.
* Alerts can be executed by jobs, so maybe it caused an error when try to find this connection between alerts and jobs.
Get triggers details
SELECT sysobjects.name AS trigger_name,
s.name AS table_schema, OBJECT_NAME(parent_obj) AS table_name,
OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate,
OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete,
OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert,
OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter,
OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof,
OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
AND t.name = 'TableName'
Get Default constraint detailes include columns
SELECT df.name as DFconstraintName, a.name as ColumnName
FROM sys.all_columns a
JOIN sys.tables t ON a.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.default_constraints df ON a.default_object_id = df.object_id
WHERE s.name = 'SchemaName'
AND t.name = 'TableName'
AND a.name = 'ColumnName'
msdb.dbo.agent_datetime
msdb.dbo.agent_datetime is translate from integer data and time numbers (in YYYYMMDD and HHMMSS formats) to datetime.
select msdb.dbo.agent_datetime(201702
select msdb.dbo.agent_datetime(201702
select msdb.dbo.agent_datetime(201702
select run_date,run_time, msdb.dbo.agent_datetime(run_
from msdb.dbo.sysjobhistory
