블로그 이미지
010-9967-0955 보미아빠

카테고리

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

달력

« » 2024.3
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' 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) '
 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'
;

-- 1GB 로 늘이기 (프러덕션은 최소 10GB 이상 확보할 것)
USE [master]
GO
ALTER DATABASE [sampledb] MODIFY FILE ( NAME = N'sampledb_log', SIZE = 1024000KB )
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 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함