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

인덱스를 다시 정리하자(조각모음)


데이터를 입력,수정,삭제를 하다보면 인덱스에 조각화가 일어난다.

조각화가 많아지면 성능이 느려진다.

 

그럼, 다시 정리를 해줘야지!!

 

인덱스를 다시 작성하는 방법에는

 

-. DBCC DBREINDEX

-. DBCC INDEXDEFRAG

-. ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)

 

이 있다..

여기에서 속도도 가장 빠르고 효율적인것이..

MS-SQL 2005 이상에서 지원하는 아래의 쿼리다.

 

ALTER INDEX ALL ON 테이블명 REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90)

 

 

테이블 하나씩 하나씩 언제하냐고??

 

한방에 해당 데이터베이스의 인덱스를 정리하는 방법을 알려 달라고???

 

해당 데이터베이스의 모든 테이블의 모든 인덱스를 재정리하는 쿼리다.

 

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

 

 

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

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) 명령을 사용하면 됩니다.


USE LAIGO

GO

DBCC SHOWFILESTATS



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

Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 880 92 LAIGO D:\LAIGO.mdf


(1개 행 적용됨)



자, 산수시간입니다.

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

1MB = 1024KB = 128(1024/8) Page = 16(1024/64) Extents


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 로 줄었습니다.


DBCC SHRINKFILE (LAIGO, 10)


Fileid FileGroup TotalExtents UsedExtents Name FileName

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

1 1 160 92 LAIGO D:\LAIGO.mdf


(1개 행 적용됨)



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


DBCC SHRINKFILE (LAIGO, TRUNCATEONLY)

DBCC SHOWFILESTATS


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

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 을 살펴봐야 할 것 같습니다.

ALTER DATABASE LAIGO SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO


DBCC SHRINKFILE(LAIGO_DATA1, 3000) -- 3GB


ALTER DATABASE LAIGO SET MULTI_USER
GO



[참고자료]

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'

MS-SQL / SQL Server

Temp DB 저장위치 변경


성능을 위해 Temp DB는 별도의 디스크에 구축해 놓는것이 좋다는 것 알고계시죠?

그런데, SQL을 설치할때 그 부분을 깜박하고 설치해 버리는 경우가 종종 발생합니다.

그런 경우에 Temp DB의 물리적 위치를 수정해야 하는데 그 때 사용하는 방법입니다.

 

1. 먼저 Temp DB의 현재 정보를 알아내야 겠죠?

select database_id,name,physical_name from sys.master_files where database_id=2

위에서 Temp DB의 mdf,ldf파일의 논리적이름을 확인할 수 있습니다.

 

2. 바꿀 경로로 수정합니다.

alter database tempdb

modify file(name=templog,filename="F:\TempDB\tempdb.ldf")

alter database tempdb

modify file(name=tempdev,filename="F:\TempDB\tempdb.mdf")

 

3. 재부팅

쿼리를 실행시키면 재부팅시켜야 적용한다고 나옵니다. 상관없다면 바로 재부팅해도 되겠지만 대부분 바로 재부팅은 힘들겠죠? 나중에 한가한 시간대나 정기점검시간에 재부팅 시켜주면 되겠습니다.

쿼리를 실행했을때 아래와 같은 에러가 나왔을때..

 

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

 

문제의 쿼리 윗부분에..

 

Set ANSI_NULLS ON
Go

 

위의 부분을 추가해서 같이 실행한다.

좀 더 자세한 사항은

 

http://support.microsoft.com/kb/296769/en-us

 

을 참고...

+ Recent posts