SQL Server - Running jobs duration

SELECT sj.name, 
sja.run_requested_date, 
DATEDIFF(SECOND, sja.start_execution_date, getdate()) as Duration
FROM msdb.dbo.sysjobactivity sja
INNER JOIN msdb.dbo.sysjobs sj ON sja.job_id = sj.job_id
WHERE (sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL) -- Running jobs
ORDER BY sja.run_requested_date desc;

SQL Server - history of failed jobs

SELECT j.[name] AS JobName,

msdb.dbo.agent_datetime(run_date, run_time) AS RunDateTime,

j.[enabled],

h.step_id, h.step_name, h.sql_message_id, h.[message], h.run_duration

FROM msdb.dbo.sysjobs j 

INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id 

WHERE j.[name] = 'Job Name'

AND h.run_status = 0 --Failed

AND msdb.dbo.agent_datetime(run_date, run_time) > '2023-02-07 00:00:00'

ORDER BY RunDateTime DESC

SQL Server - FOREIGN KEY - NOCHECK & CHECK CHECK

--drop table dbo.TestDisableFK_Pk

--drop table dbo.TestDisableFK_Fk

create table dbo.TestDisableFK_Pk

(

PKId int not null,

PkStr nvarchar(50) null,

CONSTRAINT [PK_Pk] PRIMARY KEY CLUSTERED 

(

PKId ASC

)

)

create table dbo.TestDisableFK_Fk

(

FKId int not null,

PKId int not null,

PkStr nvarchar(50) null

)

ALTER TABLE dbo.TestDisableFK_Fk  WITH CHECK ADD  CONSTRAINT [FK_Fk] FOREIGN KEY(PKId)

REFERENCES dbo.TestDisableFK_Pk (PKId)

insert into dbo.TestDisableFK_Pk (PKId) values (1), (2), (3)

insert into dbo.TestDisableFK_Fk (FKId, PKId) values (1, 1), (2, 2), (3, 3)

select * from dbo.TestDisableFK_Pk

select * from dbo.TestDisableFK_Fk

update dbo.TestDisableFK_Fk set PKId = 4 where FKId = 1

delete dbo.TestDisableFK_Pk  where PKId = 1

ALTER TABLE dbo.TestDisableFK_Fk NOCHECK CONSTRAINT [FK_Fk]

update dbo.TestDisableFK_Fk set PKId = 4 where FKId = 1

select * from dbo.TestDisableFK_Pk

select * from dbo.TestDisableFK_Fk

ALTER TABLE dbo.TestDisableFK_Fk WITH CHECK CHECK CONSTRAINT [FK_Fk]

update dbo.TestDisableFK_Pk set PKId = 4 where PKId = 1

ALTER TABLE dbo.TestDisableFK_Fk WITH CHECK CHECK CONSTRAINT [FK_Fk]

select * from dbo.TestDisableFK_Pk

select * from dbo.TestDisableFK_Fk

SQL Server - HashBytes - save encrypted password

DECLARE @NewPassword NVARCHAR(25) = N'1234';

UPDATE ...
SET [Password] = HashBytes('MD5', @NewPassword)

SQL Server - Detecting fragmentation

select iu.database_id,
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x  (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id 
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('DATABASE_NAME')   ----- CHANGE HERE
order by avg_fragmentation_in_percent desc
GO

SQL Server - database stuck in restoring status

RESTORE DATABASE <DATABASE_NAME> FROM DISK = '<Fuul path>\MyDatabase.bak' WITH REPLACE,RECOVERY

SQL Server - Case sensitive check

declare @str nvarchar(50) = N'ycuxnl'

select * from [dbo].[TableC] where StrCol = @str

select * from [dbo].[TableC] where StrCol COLLATE SQL_Latin1_General_CP1_CS_AS = @str COLLATE SQL_Latin1_General_CP1_CS_AS

SQL Server - check when stored-procedures were changed

SELECT name, create_date, modify_date 

FROM sys.objects

WHERE type = 'P' --P = stored procedures

ORDER BY modify_date DESC

SQL Server - Midnight of current date

SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))

SQL Server - ALTER DATABASE SET OFFLINE takes a long time

Problem:
ALTER DATABASE SET OFFLINE takes a long time

Solution:
Close active sessions of the database:

USE master
--find active sessions:
SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('MYDATABASE')

kill 52 .... and all other sessions

A connection was successfully established with the server, but then an error occurred during the login process

Error Message:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Solution:

SQL Server: fix Recovery Pending State of a Database

 ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO 
ALTER DATABASE [DBName] set multi_user
GO