이노의 도쿄 스토리

MS-SQL / SQL Server

백업 스크립트

제가 일본에서 일을 하다보니.. 주석이 일본어로 되어있네요.. ^^

작업설정은 이러합니다.

아래의 쿼리에서..

SET @Path = 'D:\backup'
SET @DBName = 'master'

부분에 경로와 백업하고자 하는 디비명만 적어주면 됩니다.

그러면..

D:\backup\master\master_Backup_2010-04-07_10-54.bak

이런식으로 백업경로에 백업하는 디비명 폴더를 만들고 그 안에 백업파일을 저장합니다.

백업파일은 백업시간을 파일명에 넣어서 백업을 하게 되어있습니다

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

DECLARE @Path nvarchar(200)
DECLARE @DBName nvarchar(100)

DECLARE @FileName nvarchar(100)
DECLARE @FullFileName nvarchar(500)

SET @Path = 'D:\backup'
SET @DBName = 'master'

--　経路設定
SET @Path = @Path + N'\' + @DBName

-- フォルダー生成
EXECUTE master.dbo.xp_create_subdir @Path

--　ファイル名
SET @FileName = @DBName + N'_Backup_' + CONVERT(VARCHAR(10), GETDATE(), 120) + +N'_' + LEFT(REPLACE(CONVERT(VARCHAR, GETDATE(), 108),':','-'),5)

--　経路 + ファイル名
SET @FullFileName = @Path + N'\' + @FileName + N'.bak'

-- バクアップ実行
BACKUP DATABASE @DBName TO DISK = @FullFileName WITH NOFORMAT, NOINIT, NAME = @FileName, SKIP, REWIND, NOUNLOAD, STATS = 10

MS-SQL / SQL Server

Lock(락) 자동으로 죽이기

-- 로그 테이블 생성

CREATE TABLE [dbo].[LogLockKill](
 [LogDate] [char](10) NOT NULL CONSTRAINT [DF_LogLockKill_LogDate]  DEFAULT (CONVERT([char](10),getdate(),(120))),
 [LogDateTime] [datetime] NOT NULL CONSTRAINT [DF_LogLockKill_LogDateTime]  DEFAULT (getdate()),
 [SpID] [int] NOT NULL,
 [Status] [nvarchar](30) NOT NULL,
 [LoginName] [nvarchar](128) NOT NULL,
 [HostName] [nvarchar](128) NOT NULL,
 [Blocked] [smallint] NOT NULL,
 [DbName] [varchar](30) NOT NULL,
 [Cmd] [nvarchar](16) NOT NULL,
 [WaitType] [binary](2) NOT NULL
) ON [PRIMARY]

GO

-- 인덱스 생성
CREATE NONCLUSTERED INDEX [IX_LogLockKill] ON [dbo].[LogLockKill] 
(
 [LogDate] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_LogLockKill_1] ON [dbo].[LogLockKill] 
(
 [SpID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

-- 잡으로 1분 혹은 5분등 적당한 주기로 아래의 쿼리를 돌린다.

-- 아래의 쿼리 중간 정도 에 보면.. (IF (LEN(@spid) > 0 AND Rtrim(@loginame) = 'accountforweb')) 이라는 부분이 있습니다.

-- 혹시 특정 계정에 한해서 lock(락)이 걸리는걸 죽이고 싶으시다면 accountforweb 부분에 그 계정명을 적으시면 됩니다.

-- 개인적으로는 특정계정에 한해서 하는게 좋을것 같습니다.

-- 대용량 데이터베이스에서 잡에서 집계하는 데이터양이 많다거나 여러가지 이유로 끊겨버리면 안되니까요.

DECLARE @spid int, @status nvarchar(30), @loginame nvarchar(128), @hostname nvarchar(128), @blocked smallint, @dbname varchar(30), @cmd nvarchar(16), @waittype binary(2)
DECLARE @StrSql nvarchar(20)
DECLARE @IsOK bit

SET @IsOK = 0

DECLARE DB_Cursor CURSOR FOR 
 SELECT spid, status, loginame, hostname, blocked, db_name(dbid) dbname, cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked = 0
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @spid , @status , @loginame , @hostname , @blocked , @dbname , @cmd , @waittype
WHILE @@FETCH_STATUS = 0
BEGIN

 
 
 BEGIN TRY
--  IF (LEN(@spid) > 0 AND Rtrim(@loginame) = 'accountforweb')

IF (LEN(@spid) > 0 )
  BEGIN
   
   SET @StrSql = 'KILL ' + CONVERT(VARCHAR, @spid)
   EXEC sp_executesql @StrSql    
   
   IF @@ERROR = 0
    SET @IsOK = 1
  END
 END TRY
 
 BEGIN CATCH
  SET @IsOK = 0

--  SELECT ERROR_NUMBER()
--  SELECT ERROR_MESSAGE()
 END CATCH

 IF (@IsOK = 1)
 BEGIN
  INSERT INTO dbo.LogLockKill (SpID, Status, LoginName, HostName, Blocked, DbName, Cmd, WaitType)
  VALUES (@spid , @status , Rtrim(@loginame) , Rtrim(@hostname) , @blocked , @dbname , @cmd , @waittype)

  SET @IsOK = 0
 END

 FETCH NEXT FROM DB_Cursor
 INTO @spid , @status , @loginame , @hostname , @blocked , @dbname , @cmd , @waittype

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

MS-SQL / SQL Server

SQL Server 데이터 파일 축소

SQL Server 2005 에서 데이터(mdf,ndf) 파일을 축소하는 방법에 대해서 아래와 같이 정리하였습니다.

데이터 파일의 할당 정보는 DBCC SHOWFILESTATS 명령을 사용하여 확인할 수 있습니다. 트랜잭션 로그 파일의 현재 사용 가능한 빈공간을 조회할 때 DBCC SQLPERF(LOGSPACE) 명령을 사용하면 됩니다.

아래와 같이 LAIGO 데이터베이스의 데이터 파일 LAIGO 의 전체 크기와 사용량을 확인할 수 있습니다. 

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 880 92 LAIGO D:\LAIGO.mdf

(1개 행 적용됨)

자, 산수시간입니다.

SQL Server 의 기본 저장 단위 Page 의 크기는 8KB, 이며 8개의 연속 페이지로 크기가 고정된 Extent 로 저장됩니다.

TotalExtents 값이 880 Extents 면, 1MB가 16 Extents 이므로 880/16 = 55 MB 가 됩니다.

UsedExtents 값이 92 면, 92/16 = 5.75 MB 가 됩니다.

즉, 현재 할당된 데이터 파일의 크기는 55MB 입니다만 실제 사용량은 5.75MB 라는 것을 알 수 있습니다. 


불필요한 공간을 줄이기 위해 데이터 파일을 축소하는 방법은 아래와 같습니다. LAIGO 파일의 크기를 10MB 로 축소하라는 의미입니다. 실제 TotalExtents 가 160 즉, 10MB 로 줄었습니다.

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 160 92 LAIGO D:\LAIGO.mdf

(1개 행 적용됨)

TRUNCATEONLY 를 사용해 보면 어떨까요?

아직 사용되지 않은 뒷부분 빈공간이 싹뚝 잘려 나갔습니다.

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 92 92 LAIGO D:\LAIGO.mdf

(1개 행 적용됨)

Single user 모드가 아니어도 작업은 가능합니다만 가능하면 Single User 모드가 아닌 상태에서 Deadlock 이 발생하는 사례가 있으므로 급한 상황이 아니라면 Single user 모드에서 작업하는 게 좋을 것 같다는 의견입니다. 만약 작업이 실패할 경우에는 SQL Error Log 을 살펴봐야 할 것 같습니다.

[참고자료]

DBCC SHRINKFILE(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms189493(SQL.90).aspx

MS-SQL / SQL Server

미러링 강제종료

alter database 데이터베이스명 Set Partner off

MS-SQL / SQL Server

링크드서버(Linked Server) 리스트

SELECT * FROM sys.servers A JOIN sys.linked_logins B
ON A.server_id = B.server_id AND A.is_linked = 1

난 그냥 이정도로만...

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

SELECT A.name, A.product, A.provider, A.data_source, location, provider_string, A.modify_date LinkedServerUpdateDate
, C.name, B.remote_name, B.modify_date UserUpdateDate
FROM sys.servers A JOIN sys.linked_logins B
ON A.server_id = B.server_id 
LEFT OUTER JOIN sys.server_principals C
ON B.local_principal_id = C.principal_id
ORDER BY A.name

MS-SQL / SQL Server

MS-SQL에서 대소문자 구분하기

ex) 값이 abc이고 비교값이

　1. 'abc'

　2. 'ABC'

　3. 'aBc'

　4. 'aBC'

　5. 'abc '

일 경우

기본적으로 설치시 어떤 설정을 하면 비교가 된다고 한다...

하지만 확인된 결과가 아니라서 이곳에 작성하지는 않았다.

Test1. 일반적으로 테스트 할 경우

　1. 같음　　　2. 같음　　　3. 같음　　　4. 같음　　　5. 같음

Test2. BINARY_CheckSum을 이용 할 경우

　1. 같음　　　2. 틀림　　　3. 틀림　　　4. 틀림　　　5. 같음

Test3. collate Latin1_General_CS_AI_KS_WS를 이용 할 경우

　1. 같음　　　2. 틀림　　　3. 틀림　　　4. 틀림　　　5. 같음

결론...

　BINARY_CheckSum와 collate Latin1_General_CS_AI_KS_WS를 이용하되

　빈칸이 안들어가도록 막으면...Ok...

　너무 간단한 결론인가...ㅡㅡ;;

MS-SQL / SQL Server

백업정보 얻기 - 백업한 데이터베이스에 대한 수행된 백업들의 정보를 얻는다.

USE msdb;

GO

SELECT backup_start_date, type, physical_device_name, backup_set_id

FROM backupset bs inner join backupmediafamily bm

ON bs.media_set_id = bm.media_set_id

WHERE database_name = '[데이터베이스 명]'

ORDER BY backup_start_date desc

MS-SQL / SQL Server

MSSQL 서버, 데이터베이스 유져 리스트

서버의 유저 리스트

select * from sys.server_principals where type = 'S'

데이터베이스의 유저 리스트

SELECT * FROM sys.database_principals

name컬럼과 default_schema_name 컬럼을 이용하시면 될듯 합니다.

인터넷에 검색되는건 아래의 이상한 값만 나오는 것만 나와있네요..

SELECT u.name as UserName, s.name as SchemaName 

FROM sys.sysusers u JOIN sys.schemas s ON u.uid = s.principal_id

MS-SQL / SQL Server

컬럼타입 변경하기

MSSQL 에서 컬럼타입을 변경하는 쿼리입니다.

ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 [변경할 컬럼타입]

예>

ALTER TABLE dbo.Test ALTER COLUMN UserID varchar(20)

MS-SQL / SQL Server

프로시저 생성일, 수정일, 내용 출력하기

SELECT

CONVERT(varchar(10), getdate(), 120) LogDage

, getdate() AS LogDateTime

, A.name

, A.type_desc

, A.create_date

, A.modify_date

, B.definition

FROM sys.objects AS A JOIN sys.sql_modules AS B

ON A.object_id = B.object_id

WHERE A.schema_id = 1 AND type = 'P'

MS-SQL / SQL Server

프로시져 내용 확인하기

해당 프로시저의 내용을 확인할 수 있다.

definition 컬럼을 확인.

SELECT * FROM sys.sql_modules WHERE OBJECT_ID = OBJECT_ID('프로시저명');

MS-SQL / SQL Server

프로시저 생성일/수정일 확인하기

SELECT

CONVERT(varchar(10), getdate(), 120) LogDage

, getdate() AS LogDateTime

, A.name

, A.type_desc

, A.create_date

, A.modify_date

FROM sys.objects AS A JOIN sys.sql_modules AS B

ON A.object_id = B.object_id

WHERE A.schema_id = 1 AND type = 'P'