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)

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