Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Troubleshoot Microsoft SQL Server memory consumption issues - DBCC MEMORYSTATUS

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_constraints
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_constraints t
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'
...

sys.configurations - (SQL) Server configuration

select * from [sys].[configurations];


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_jobs 1,garbage  

; 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_Run_Date, r.Next_Run_Time) as JobStart,
       DATEDIFF(second, msdb.dbo.agent_datetime(r.Next_Run_Date, r.Next_Run_Time), getdate()) as JobDuration_Seconds,
       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_Run_Date, r.Next_Run_Time), getdate()) as money)
              -
              cast(a.AvgJobDuration_Seconds as money)
       ) > @MinDelaySeconds
AND    -- X% mare then the average duration
       (      cast(DATEDIFF(second, msdb.dbo.agent_datetime(r.Next_Run_Date, r.Next_Run_Time), getdate()) as money)
              /
              cast(a.AvgJobDuration_Seconds as money)
       ) > (1+@MinDelayPercent)
--ORDER BY name,next_run_date,next_run_time

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,3),1) + ':' + right(s.active_start_time,2) as char (8))
                     WHEN 4 THEN cast('00:' + Left(right(s.active_start_time,4),2)  + ':' + right(s.active_start_time,2) as char (8))
                     WHEN 5 THEN cast('0' + Left(right(s.active_start_time,5),1) + ':' + Left(right(s.active_start_time,4),2)  +':' + right(s.active_start_time,2) as char (8))
                     WHEN 6 THEN cast(Left(right(s.active_start_time,6),2) + ':' + Left(right(s.active_start_time,4),2) + ':' + right(s.active_start_time,2) as char (8))
              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.#RunningJobs') IS NOT NULL
      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_jobs 1,garbage  

SELECT j.job_id, j.name AS JobName,
       (CASE WHEN next_run_date=THEN '[Not scheduled]' ELSE CONVERT(VARCHAR,msdb.dbo.agent_datetime(r.Next_Run_Date, r.Next_Run_Time)) END) as JobStart
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.#RunningJobs') IS NOT NULL
      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(20170205, 0)
select msdb.dbo.agent_datetime(20170205, 123122)
select msdb.dbo.agent_datetime(20170205, 112)

select run_date,run_time, msdb.dbo.agent_datetime(run_date,run_time)
from msdb.dbo.sysjobhistory