이노의 도쿄 스토리

MS-SQL / SQL Server

컴퓨터이름 변경했을때 MSSQL 서버이름 변경하기

컴퓨터이름을 변경했을때, MS-SQL 서버의 이름도 변경해야한다.

현재 MS-SQL 서버이름을 확인 하는 방법

SELECT @@servername

일단 위의 쿼리에서 서버이름으로 등록되어있는 정보를 삭제한다.

서버이름 삭제

SP_DROPSERVER '현재의 MSSQL서버이름'

새로운 서버이름 추가

SP_ADDSERVER '현재 컴퓨터이름(새로운이름)', 'local'

이렇게 하고 서비스 재시작 한번 해주고..

다시  MS-SQL 서버 이름을 확인해보면..

SELECT @@servername

변경된것을 알수 있다.

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

SQL Server 2005에서 DBCC SHRINKFILE 문을 사용하여 

트랜잭션 로그 파일을 축소하는 방법

Microsoft SQL Server 2005에서는 데이터베이스의 트랜잭션 로그 파일을 축소하여 사용하지 않는 페이지를 제거할 수 있습니다.

데이터베이스 엔진에 의해 공간이 효율적으로 다시 사용되지만 트랜잭션 로그 파일이 예기치 않게 커지면 트랜잭션 로그 파일을 수동으로 축소해야 할 수 있습니다.

이 문서에서는 DBCC SHRINKFILE 문을 사용하여 SQL Server 2005 데이터베이스의 전체 복구 모델에서 트랜잭션 로그 파일을 수동으로 축소하는 방법을 설명합니다.

SQL Server 2005에서 트랜잭션 로그 파일을 축소하는 데 사용하는 방법은 SQL Server 2000에서 트랜잭션 로그 파일을 축소하는 데 사용하는 방법과 다를 수 있습니다.

일단, 트랜잭션 로그 파일을 축소하기 전에 로그 파일에 데이터가 들어가 있으면 축소가 되지 않으므로, 로그 파일 내에 데이터를 비우는 작업을 해야합니다.

트랜잭션 로그 데이터를 지우는 방법은 2가지 방법이 있습니다.

기본적으로 트랜잭션 로그는 백업을 받으면 비우게 되어있습니다.

그래서 트랜잭션 로그 백업을 진행합니다.

여기에서 파일로 백업을 받을 것인지 그냥 데이터만 지울것인지를 선택하면 됩니다.

1.트랜잭션 로그를 파일로 백업

BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

예> BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

2.트랜잭션 로그를 파일로 백업안하고 그냥 비우기

BACKUP LOG [데이터베이스명] WITH TRUNCATE_ONLY

위의 쿼리를 실행하면 트랜잭션 로그 파일 내의 데이터를 비우게 됩니다.

하지만, 파일크기는 그대로 이기 때문에 파일 크기를 축소해야할 필요가 있습니다.

트랜잭션 로그 파일 크기 축소하기

USE [데이터베이스명];
GO

DBCC SHRINKFILE (트랜잭션로그명, 파일크기(단위:MB));

GO

예> TempDB 데이터베이스의 트랜잭션 로그 파일명 templog 를 1MB 크기로 축소하는 예..

USE TempDB;
GO

DBCC SHRINKFILE (templog, 1);

GO

데이터베이스 파일명 찾기

sp_helpfile

GO

위의 쿼리를 실행하면 트랜잭션 로그명을 알수 있습니다.

MS-SQL / SQL Server

각 데이터베이스의 로그 공간 사용률 확인하기

트랜잭션 로그의 현재 크기 및 각 데이터베이스의 로그 공간 사용률을 반환합니다.

이 정보를 사용하여 트랜잭션 로그에서 사용된 공간의 크기를 모니터링할 수 있습니다.

쿼리 실행창에서

DBCC SQLPERF(LOGSPACE)

GO

을 실행합니다.

MS-SQL / SQL Server

테이블별 레코드 수 알아내기

SELECT o.name, i.rows

FROM sysindexes i

INNER JOIN sysobjects o ON i.id = o.id

WHERE i.indid < 2 

AND o.xtype = 'U'

ORDER BY o.name;

MS-SQL / SQL Server

해당 데이터베이스의 테이블 용량 확인하기

TableSize의 단위는 MB 입니다.

select convert(varchar(100), min(o.name)) TableName

, (sum(reserved) * (8192/1024))/1024 TableSize

, (sum(reserved) * (8192/1024))/1024. TableSizeDetail

from sysindexes i 

inner join sysobjects o on (o.id = i.id) 

where i.indid in (0, 1, 255) 

and o.xtype = 'U' 

group by i.id 

order by TableName

MS-SQL / SQL Server

연결된 서버 만들기 스크립트 (Linked Server Script) 

MSSQL 에서 Linked Server Script 예제입니다. 

/****** 개체:  LinkedServer [CUSTOMER]    스크립트 날짜: 10/28/2008 10:30:50 ******/ 

EXEC master.dbo.sp_addlinkedserver @server = N'CUSTOMER', @srvproduct=N'CUSTOMER', @provider=N'SQLNCLI', @datasrc=N'10.10.10.7', @catalog=N'기본접속DB명' 

 /* For security reasons the linked server remote logins password is changed with ######## */ 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CUSTOMER',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='ipark' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'collation compatible', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'data access', @optvalue=N'true' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'dist', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'pub', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'rpc', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'rpc out', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'sub', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'connect timeout', @optvalue=N'0' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'collation name', @optvalue=null 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'lazy schema validation', @optvalue=N'false' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'query timeout', @optvalue=N'0' 

GO 

EXEC master.dbo.sp_serveroption @server=N'CUSTOMER', @optname=N'use remote collation', @optvalue=N'true' 

Server 이름과 아이피만 바꿔주시고 실행하시면 되겠습니다.