CopyPaste.Net: mssql

Showing posts with label mssql. Show all posts

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

Row counts in all tables

SELECT

QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

, SUM(sPTN.Rows) AS [RowCount]

, IDENT_CURRENT( sOBJ.name ) as [CurrentMaxId]

FROM

sys.objects (nolock) AS sOBJ

INNER JOIN sys.partitions (nolock) AS sPTN

ON sOBJ.object_id = sPTN.object_id

WHERE

sOBJ.type = 'U'

AND sOBJ.is_ms_shipped = 0x0

AND index_id < 2 -- 0:Heap, 1:Clustered

GROUP BY

sOBJ.schema_id

, sOBJ.name

ORDER BY [CurrentMaxId] desc, [RowCount] desc

SSIS - get packages versions and data

SELECT prog.project_id, f.[name], *
FROM SSISDB.catalog.packages pkg
JOIN SSISDB.catalog.Projects prog ON pkg.project_id = prog.project_id
JOIN SSISDB.catalog.Folders f ON proj.folder_id = f.folder_id
WHERE prog.[name] = N'PROJECTNAME'

SQL Server: Check if Service Broker is enabled

SELECT name, database_id, is_broker_enabled
FROM sys.databases
--WHERE name = 'DATABASENAME'
--WHERE database_id = 14

SSMS: sql server Script failed for StoredProcedure 'PROCEDURENAME' (Microsoft.SqlServer.Smo) ... Invalid version: ...

What I tried to do:
Alter Stored Procedure in SSMS.

Error:
sql server Script failed for StoredProcedure 'PROCEDURENAME' (Microsoft.SqlServer.Smo) ... Invalid version: ...

Solution:
Update SSMS to a newer release.

gMSA and SQL Server

Overview for gMSA - see in this post.

gMSA and SQL Server

A single gMSA can be shared across multiple SQL Server hosts.
It helps to manage the service accounts in environments with a large number of SQL Server instances, without compromise on security issues.
Any update to an MSA password does not require a restart of SQL Server.
gMSA can be very interesting for an availability group environment

The main point of gMSA for AG is the setup of one managed service account for all replicas.

All instances in AG should be in the gMSA group.

gMSA for AG is only supported from SQL Server 2016.

Connecting gMSA with SQL Server
A connection between SQL Server to gMSA is created by assigning the gMSA account to the SQL Server service.
The connection is done:

For a new installation: During the SQL Server installation you specify the gMSA account.

For an existing SQL Server instance: changing the existing SQL Server instance to use a gMSA is done with the SQL Server Configuration Manager (SSCM) tool.

gMSA requirements
gMSA requirements - see here.

Additional requirements for gMSA for SQL Server:

SQL Server 2014 and above

SQL Server support for gMSA when running on Windows Server 2012 R2 and above.

Domain Functional Level of 2012 or higher.

Active Directory PowerShell module installed.

Useful links

Using Group Managed Service Accounts with SQL Server

Configure the SQL Server instance to use the gMSA

Managed Service Accounts with SQL Server

Using a gMSA with SQL Server