블로그 이미지
보미아빠

카테고리

보미아빠, 석이 (524)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total
Today
Yesterday

달력

« » 2025.7
1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31

공지사항

최근에 올라온 글

개념 이해를 위해 mdf, ndf 파일은 OS에서 받은 데이터베이스 공간이다. 이 공간은 여러 테이블이나 인덱스 등이 사용한다. 테이블이 어떤 이유로 매우 커졌다가 내부 데이터를 delete로 지우면 할당을 해지한다. 이 때 해지한 공간은 완전히 할당 해지 되는 경우가 별로 없고 그 공간은 해당 테이블이나 인덱스에서만 재활용 가능하다. 이를 다른 테이블이나 인덱스에서도 사용 가능하게 하려면 heap rebuild, clustered index rebuild, index rebuild 등을 해 완전히 할당 해지 해주어야 한다. 또 다른 방법은 reorganize 하는 방법이 있다.  이렇게 다른 테이블이나 인덱스도 사용할 수 있는 공간이 되면 비로서 mdf 파일의 크기를 줄여 OS로 공간을 반환할 수도 있다. reorganize 는 하드 디스크 조각 모음으로 이해하면 비슷하다. rebuild 는 순차 읽기 성능이 좋고 reorganize 는 순차 읽기 성능에 좋지 않다. 그런데, 요즘 대부분 SSD를 쓰니 논리 순서와 할당 순서의 일관성이 성능에 영향을 끼치지 않는다. 

 

 

아래 결과에서 unallocated space 와 unused 사이즈에 주목한다. 

 

sp_spaceused 

database_name                       database_size         unallocated space
------------------------------      ------------------    ------------------
SqlLogManDmvRepositoryCafeV3        5354120.00 MB         66097.92 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
5411846224 KB      549654168 KB       178159848 KB       4684032208 KB


Completion time: 2024-01-08T15:50:42.0522621+09:00


--> 인덱스 리빌드 스크립트 수행 후 (6시간)
-- 인덱스 리빌드는 unused 사이즈를 unallocated space 로 반환하게 된다. 

sp_spaceused 

database_name                       database_size         unallocated space
------------------------------      ------------------    ------------------
SqlLogManDmvRepositoryCafeV3        5440840.00 MB         4692463.71 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
760496424 KB       574814688 KB       184509664 KB       1172072 KB

 

줄이기 늘이기

-- 조회
select * from sysaltfiles where dbid = db_id()

-- TargetSize 는 무시된다. 사이즈는 MB 단위
-- 2022 에는 WAIT_AT_LOW_PRIORITY 로 장기 수행 Sch-S의 영향을 줄일수 있다. 
DBCC SHRINKFILE ('SqlLogManDmvRepositoryCafeV3', 0, TRUNCATEONLY);

-- 늘이는 명령어 
ALTER DATABASE SqlLogManDmvRepositoryCafeV3 MODIFY FILE ( NAME = 'SqlLogManDmvRepositoryCafeV3', SIZE = 1TB )

 

reorganize 방법 (수정 좀 해서 써야 할 듯)

 

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
 WHERE TABLE_TYPE = 'BASE TABLE' 
	--and TABLE_NAME not in (
	--	select distinct object_name(object_id) --, name, type_desc
	--	from sys.indexes 
	--	where 
	--		allow_row_locks = 0 
	--		or allow_page_locks = 0) 

 ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REORGANIZE '
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

 

온라인에 사용 불가능하다. 엔터프라이즈는 온라인 옵션 가능 (수정 좀 해서 써야 할 듯)

 

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90, ONLINE = ON)
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor
Posted by 보미아빠
, |

 

-- size 
select 
	 b.name dbname
	, a.name dbnameDetail
	, c.recovery_model_desc 
	, c.log_reuse_wait_desc 
	, c.user_access_desc 
	, a.fileid
	, a.groupid
	, a.size
	, a.maxsize
	, a.growth
	, a.status
	, a.perf
	, a.filename
	, computedsizemb = cast(a.size / 128. as int)
	, computedgrowth = case when a.status & 0x100000 = 0x100000 then a.growth else cast(a.growth / 128. as int) end
	, computedgrowthunit = case when a.status & 0x100000 = 0x100000 then 'percent' else 'mb' end
	, d.first_lsn, d.last_lsn, d.checkpoint_lsn, d.database_backup_lsn, d.backup_finish_date
from master.dbo.sysaltfiles a  with (nolock)
	join master.dbo.sysdatabases b  with (nolock)
		on a.dbid = b.dbid
	join master.sys.databases  c 
		on a.dbid = c.database_id 
	outer apply 
		(
		select top 1 first_lsn,	last_lsn,	checkpoint_lsn,	database_backup_lsn, backup_finish_date 
		from msdb.dbo.backupset 
		where database_name = db_name(a.dbid)
		order by backup_finish_date desc 
		) d
where b.name not in ('master', 'msdb', 'tempdb', 'model', 'lazylog') 


-- ****************************************

dbcc sqlperf(logspace) -- 사용량
go

use sampledb 
go
dbcc loginfo -- 로그파일 확인 
go

-- 혹은 아래 명령으로 확인 
-- select * from sys.dm_db_log_info(6)
-- 6은 select * from sysdatabases의 해당 dbid입니다. 

use sampledb 
go

-- 파일 끝에서 자르기  
-- truncateonly 옵션이 들어가면 사이즈는 무시된다. 그래서 0
-- 백업되지 않으면 비활성 상태로 사용된다. 그러므로 백업 후에 자르거나
-- reocvery level 을 simple 로 하고 자른 후 원래 recovery level 로 변경한다. 
DBCC SHRINKFILE ('sampledb_log', 0, TRUNCATEONLY);
go 


-- 로그파일의 끝을 변경시킴 (linux 의 dev/null 로 백업)
-- 실제 프러덕션 환경은 파일 이름 바꾸어서 수행해야 함
backup log sampledb to 
disk ='NUL' with stats = 1 
;

-- 1GB 로 늘이기 (프러덕션은 최소 10GB 이상 확보할 것)
USE [master]
GO
ALTER DATABASE [sampledb] MODIFY FILE ( NAME = N'sampledb_log', SIZE = 1GB, FILEGROWTH = 512MB)
GO
Posted by 보미아빠
, |

미러링

카테고리 없음 / 2024. 2. 2. 11:10
--------------------------------------------
-- full & transaction log backup and norecovery mode
--------------------------------------------

-- principal server
backup database mirrortest to disk = 'd:\mssql\share\mirrortest.full'
backup log mirrortest to disk = 'd:\mssql\share\mirrortest.log'

-- mirror server
restore database mirrortest from disk = '\\ip\share\mirrortest.full' with norecovery 
restore log mirrortest from disk = '\\ip\share\mirrortest.log' with norecovery

--------------------------------------------
-- mirror mirroring endpoint add
--------------------------------------------

CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

--------------------------------------------
-- principal mirroring endpoint add
--------------------------------------------
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

--------------------------------------------
-- mirroring patner setting (mirror server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'

--------------------------------------------
-- mirroring patner setting (principal server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'

--------------------------------------------
-- mirroring start (principal server)
--------------------------------------------
alter database mirrortest set safety full


--------------------------------------------
-- ** mirroring off
--------------------------------------------
alter database mirrortest set partner off

--------------------------------------------
-- ** mirroring failover
--------------------------------------------
alter database mirrortest set partner failover 

--------------------------------------------
-- ** mirroring suspend 
--------------------------------------------
alter database mirrortest set partner suspend 

--------------------------------------------
-- ** mirroring suspend 
--------------------------------------------
alter database mirrortest set partner resume 

--------------------------------------------
-- ** mirroring endpoint
--------------------------------------------
SELECT name, port FROM sys.tcp_endpoints;  

--------------------------------------------
-- ** mirroring status
--------------------------------------------

select 
	db.name, 
	db.state_desc, 
	dm.mirroring_role_desc, --**
	dm.mirroring_state_desc, --**
	dm.mirroring_safety_level_desc, --**
	dm.mirroring_partner_name, 
	dm.mirroring_partner_instance 
from sys.databases db
	inner join sys.database_mirroring dm
	on db.database_id = dm.database_id
where dm.mirroring_role_desc is not null
order by db.name


--------------------------------------------
-- ** go home~
--------------------------------------------
ALTER DATABASE [databaseName] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Posted by 보미아빠
, |

암호화

카테고리 없음 / 2023. 11. 17. 11:22

아래 github 소스는 어플리케이션과 SQL 어디에서든 암호화 복호화가 가능한 방법입니다. 

 

sql(암호화) -> sql(복호화)

c#(암호화) -> c#(복호화)

sql(암호화) -> c#(복호화)

c#(암호화) -> sql(복호화)

 

https://github.com/krcs/SQLServerCrypto

-- sql sample 

 

EncryptByPassPhrase 와 DecryptByPassPhrase 를 이용한 암호화 방법

-- https://docs.microsoft.com/ko-kr/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-ver16

 

IF OBJECT_ID ('PasswordKey') IS NOT NULL

DROP FUNCTION dbo.PasswordKey

GO

 

CREATE FUNCTION PasswordKey()

RETURNS varchar(100)

WITH ENCRYPTION

AS

BEGIN

RETURN 'P@ssw0rd'

END

go

 

SELECT EncryptByPassPhrase(dbo.passwordkey(), 'target message')

GO

-- 결과

-- 0x0100000093B09F6A22BD125C758C41C0B7C1EF5EA5433F5B31FD71E3D3CFF8C073C2ECEB

 

SELECT CAST(DecryptByPassPhrase(dbo.passwordkey(), 0x01000000068564F4F8218FE14236AD6EA97FD0EA6A9146C91C2F7ABDC69FE7E96AB7CDB8) as varchar(8000))

-- 결과

-- target message

 

 

SQLServerCryptoConsole.zi_
0.14MB

Posted by 보미아빠
, |

# TLS 1.0, 1.1 을 서버에서 disable 하는게 문제가 아니라 client 가 TLS 1.0, 1.1 을 써 통신하는게 있다면, 서버에서 막으면 장애난다.

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f

Posted by 보미아빠
, |

# sweet32 취약점 해결을 위해 아래 regedit 에서 function 에서 3가지를 삭제하고 물리 서버를 재시작 한다. 
KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Cryptography\Configuration\Local\SSL\00010002 이 값 편집
TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA
TLS_DHE_RSA_WITH_AES_256_CBC_SHA
TLS_DHE_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_256_GCM_SHA384
TLS_RSA_WITH_AES_128_GCM_SHA256
TLS_RSA_WITH_AES_256_CBC_SHA256
TLS_RSA_WITH_AES_128_CBC_SHA256
TLS_RSA_WITH_AES_256_CBC_SHA
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_3DES_EDE_CBC_SHA * 제거
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256
TLS_DHE_DSS_WITH_AES_256_CBC_SHA
TLS_DHE_DSS_WITH_AES_128_CBC_SHA
TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA
TLS_RSA_WITH_RC4_128_SHA * 제거 
TLS_RSA_WITH_RC4_128_MD5 * 제거 
TLS_RSA_WITH_NULL_SHA256
TLS_RSA_WITH_NULL_SHA
TLS_PSK_WITH_AES_256_GCM_SHA384
TLS_PSK_WITH_AES_128_GCM_SHA256
TLS_PSK_WITH_AES_256_CBC_SHA384
TLS_PSK_WITH_AES_128_CBC_SHA256
TLS_PSK_WITH_NULL_SHA384
TLS_PSK_WITH_NULL_SHA256


# 아래 처럼 만들기 
TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA
TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA
TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA
TLS_DHE_RSA_WITH_AES_256_CBC_SHA
TLS_DHE_RSA_WITH_AES_128_CBC_SHA
TLS_RSA_WITH_AES_256_GCM_SHA384
TLS_RSA_WITH_AES_128_GCM_SHA256
TLS_RSA_WITH_AES_256_CBC_SHA256
TLS_RSA_WITH_AES_128_CBC_SHA256
TLS_RSA_WITH_AES_256_CBC_SHA
TLS_RSA_WITH_AES_128_CBC_SHA
TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
TLS_DHE_DSS_WITH_AES_128_CBC_SHA256
TLS_DHE_DSS_WITH_AES_256_CBC_SHA
TLS_DHE_DSS_WITH_AES_128_CBC_SHA
TLS_DHE_DSS_WITH_3DES_EDE_CBC_SHA
TLS_RSA_WITH_NULL_SHA256
TLS_RSA_WITH_NULL_SHA
TLS_PSK_WITH_AES_256_GCM_SHA384
TLS_PSK_WITH_AES_128_GCM_SHA256
TLS_PSK_WITH_AES_256_CBC_SHA384
TLS_PSK_WITH_AES_128_CBC_SHA256
TLS_PSK_WITH_NULL_SHA384
TLS_PSK_WITH_NULL_SHA256

Posted by 보미아빠
, |

# sql 2016 는 SP3 업그레이드 필요 (CSAP), 아래내용 적용
# sql 2017 19는 할 필요 없음
# New-SelfSignedCertificate 생성
New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" `
-DnsName ("{0}" -f [Systehttp://m.Net.Dns]::GetHostByName($env:computerName).HostName),'localhost' `
-KeyAlgorithm "RSA" -KeyLength 2048 -HashAlgorithm "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" `
-NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider" `
-CertStoreLocation "cert:\LocalMachine\My"

# 결과 
PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\My

Thumbprint                                Subject                                                                                                                                   
----------                                -------                                                                                                                                   
aaa5E3ddd742dd7AE1D4A8C648FC556F5B9D90003  CN=HOSTNAME.....                                                                                       

# 서버의 인증서 조회 
dir cert:\LocalMachine\My
mmc 에서 인증서 항목을 추가하고 local computer 의 인증서를 조회해 export 가능
인증서에 해당 사용자 권한을 추가해 줘야 함

# SQL Server 2016 의 경우 아래의 방법으로 등록 (SqlServerConfigurationManager 에서 로딩이 안되더라)
$regPath = 'HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
$thumbprint = 'aaa5E3ddd742dd7AE1D4A8C648FC556F5B9D90003'
Set-ItemProperty $regPath -Name 'Certificate' -Value $thumbprint
# 이건 강제하고 싶을때만 하면 된다. 
#Set-ItemProperty $regPath -Name 'ForceEncryption' -Value 1 -Type Dword

# sql 2019 이상에서는 네트워크 설정에서 인증서 로딩 (로딩 전 이전 서버의 인증서 복구 해두어야 한다.)
# 아래 링크 2개 잘 읽어봐~
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver15#step-2-configure-encryption-settings-in-sql-server

# SqlServerConfigurationManager 에서 restart

Posted by 보미아빠
, |

데드락 발생시 extended events 로 캡처 후 내용을 xdl로 저장하면 아래와 같은 그래프로 볼 수 있다. 

잠금 리소스가 "개체 잠금"이라는 특이한 현상 발생

 

 

waitresource =  OBJECT: 5:1829893886:8 이거라고 한다. (databaseid, objectid, lockPartition)

clustered type primary key만 있는 테이블이며, 형 변환 없이 정확히 쿼리하는데 deadlock 이 발생함

 

윈인은 테이블 생성시 allow_row_locks 와 allow_page_locks 를 disable 해두었음.

 

 

해결 방법

ALTER INDEX PK_XX ON XX
REBUILD WITH (ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )

 

Deadlock XDL

<deadlock>
 <victim-list>
  <victimProcess id="process19727d3bc28" />
 </victim-list>
 <process-list>
  <process id="process19727d3bc28" taskpriority="0" logused="0" waitresource="OBJECT: 5:1829893886:8 " waittime="616" ownerId="132371886315" transactionname="DELETE" lasttranstarted="2023-09-25T09:34:29.420" XDES="0x27c1e900460" lockMode="X" schedulerid="20" kpid="1476" status="suspended" spid="9848" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-25T09:34:29.420" lastbatchcompleted="2023-09-25T09:34:29.420" lastattention="1900-01-01T00:00:00.420" clientapp="Microsoft JDBC Driver for SQL Server" hostname="avdataapi031.cafe" hostpid="0" loginname="xx" isolationlevel="read uncommitted (1)" xactid="132371886315" currentdb="5" currentdbname="xxBBS5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="132" stmtend="550" sqlhandle="0x02000000dfb0370dbca820a19a4c8bdee0c0272fe194f8980000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DELETE FROM dbo.XX
        WHERE         xxid = @P0
          AND         startDate = CAST(@P1 AS CHAR(8))
          AND        endDate = CAST(@P2 AS CHAR(8))
          AND         memberId = CAST(@P3 AS VARCHAR(20))                                   </inputbuf>
  </process>
  <process id="process1972770f848" taskpriority="0" logused="0" waitresource="OBJECT: 5:1829893886:0 " waittime="616" ownerId="132371886316" transactionname="DELETE" lasttranstarted="2023-09-25T09:34:29.420" XDES="0x1938d11c460" lockMode="X" schedulerid="9" kpid="10696" status="suspended" spid="10300" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-25T09:34:29.420" lastbatchcompleted="2023-09-25T09:34:29.420" lastattention="1900-01-01T00:00:00.420" clientapp="Microsoft JDBC Driver for SQL Server" hostname="avdataapi019.cafe" hostpid="0" loginname="xx" isolationlevel="read uncommitted (1)" xactid="132371886316" currentdb="5" currentdbname="xxBBS5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="132" stmtend="550" sqlhandle="0x02000000dfb0370dbca820a19a4c8bdee0c0272fe194f8980000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DELETE FROM dbo.XX
        WHERE         xxid = @P0
          AND         startDate = CAST(@P1 AS CHAR(8))
          AND        endDate = CAST(@P2 AS CHAR(8))
          AND         memberId = CAST(@P3 AS VARCHAR(20))                                   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="8" objid="1829893886" subresource="FULL" dbid="5" objectname="xxBBS5.dbo.XX" id="lock1905d4c0680" mode="IX" associatedObjectId="1829893886">
   <owner-list>
    <owner id="process1972770f848" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process19727d3bc28" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="1829893886" subresource="FULL" dbid="5" objectname="xxBBS5.dbo.XX" id="lock1054afcec00" mode="X" associatedObjectId="1829893886">
   <owner-list>
    <owner id="process19727d3bc28" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1972770f848" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

 

확인용 쿼리

SELECT name, allow_row_locks, allow_page_locks
FROM sys.indexes
	AND allow_row_locks = 0 
	AND allow_page_locks = 0

 

Posted by 보미아빠
, |
-- 언어 변경
cmd /c chcp 437

-- 상태 모니터링
netsh int tcp show global
netsh int tcp show chimneystats
netstat -nt | findstr /i offloaded

-- 변경
netsh int tcp set global chimney=enabled 
netsh int tcp set global chimney=disabled

 

TCP Chimney offload is deprecated technology: Windows Server 2022, Windows Server 2019, Windows Server 2016

Posted by 보미아빠
, |

https://github.com/microsoft/CSS_SQL_Networking_Tools/wiki/SQLCHECK

 

SQLCHECK

Tools used by the SQL Networking Customer Support Team - microsoft/CSS_SQL_Networking_Tools

github.com

위 프로그램을 사용해 리포트를 수집한다. 

 

SQL Server start Account 가 올바르게 SPN 등록되었는지 확인한다. 

 

    Account       SPN                                                     Has Duplicates

    ------------  ------------------------------------------------------  --------------

    computername$  MSSQLSvc/computername.test.domain.local:1433           False    

 

위는 start 계정이 아니라 컴퓨터로 등록된 경우이다. 이걸 삭제하고 올바르게 등록한다. 

 

서비스 시작 계정을 변경하기위해

 

계정을 변경하고, sql 을 원하는 시간에 재시작 하려면 아래의 과정을 테스트 해보고 적용한다. 

1) spn 을 새로운 계정으로 등록

2) services.msc 에서 아이디와 암호를 변경해둔다. 

3) 서비스 점검 시간에 해당 서버를 재시작

 

0. SPN 확인

* 컴퓨터 네임으로 spn이 등록되어 있으면 AD 계정으로 로그인이 안되는 현상이 일어난다. 

* 원인은 services.msc 에서 변경하면 이런 현상이 일어난다. 이런 현상을 발생하지 않게 하려면 계정을 sqlserver configureation manager 에서 변경해야 한다. (당황스럽네..)

 

일단 컴퓨터 이름으로 spn이 등록된것이 있는지 쿼리한다. SPN 이 hostname으로 등록되어 있으면 안된다.


setspn -T adDomain -q */hostname

 

위 프로그램을 사용하기 싫으면 sql 스타트 계정이 올바르게 등록 되어 있는지 확인 한다. 

example)

setspn -L sqlServiceAccount > c:\spn.txt

notepad c:\spn.txt

 

1. SPN 삭제

명령 프트를 관리자 권한으로 실행한 후, 다음 명령을 사용하여 SPN을 삭제합니다.

setspn -D <문제되는 SPN> <문제되는 SPN이 등록된 계정 이름>

 

example) $ 가 있는지 없는지는 위 쿼리에서 나오는 결과를 보고 판단한다. 

setspn -D MSSQLSvc/computername.test.domain.local:1433 computername$

setspn -D MSSQLSvc/computername.test.domain.local:1433 computername

 

2. 수동 등록

SQL Server 서비스 시작 계정(도메인 계정)을 사용하여 SPN을 등록합니다.

setspn -A <SPN> <SPN을 등록할 계정 이름>

 

example)

setspn -A MSSQLSvc/omputername.test.domain.local sqlServiceAccount

setspn -A MSSQLSvc/omputername.test.domain.local:1433 sqlServiceAccount

 

AD 에 합류된 서버를 재설치 할때는 AD 에서 제외시켜주고 다시 설치하고 Join 해주도록 하자

 

아래는 참고

 

Setspn

Table of contents Setspn Article 08/31/2016 In this article --> Applies To: Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows 8 Reads, modifies, and deletes the Service Principal Names (SPN) directory property for an Active Directo

learn.microsoft.com

 

 

Posted by 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함