MS-SQL / SQL Server

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


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

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

 

쿼리 실행창에서

 

DBCC SQLPERF(LOGSPACE)

GO

 

을 실행합니다.

 

열 이름정의

Database Name

로그 통계가 표시될 데이터베이스의 이름입니다.

Log Size(MB)

로그에 할당된 현재 크기입니다. SQL Server 2005 데이터베이스 엔진은 내부 헤더 정보용으로 작은 크기의 디스크 공간을 예약하므로 이 크기는 원래 로그 공간을 위해 할당된 크기보다 항상 작습니다.

Log Space Used(%)

로그 파일에서 트랜잭션 로그 정보가 현재 차지하고 있는 비율입니다.

Status

로그 파일의 상태이며 항상 0입니다.

 

MS-SQL / SQL Server

데이터베이스 파일크기 확인하기


쿼리분석기로 해당 서버에 연결한다음 

  

USE 데이터베이스명 

GO 

sp_spaceused 

GO 

를 실행하시면 현재 데이터베이스의 크기 정보를 알 수 있습니다. 

  

P.S> 

현재 트랜잭션 로그 사용량 

DBCC SQLPERF(LOGSPACE) 

  

현재 데이터베이스 파일별 크기 

sp_helpfile 

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

 

을 참고...

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 이름과 아이피만 바꿔주시고 실행하시면 되겠습니다. 

MS-SQL / SQL Server

해외 IP 접속에 대한 개발 건 - GeoIP 


첨부파일의 csv 는 웹에서 검색하여 찾아낸 IP 국가별 대역으로 유상으로 업그레이드 해주는 해외 사이트가 있습니다. 이 파일은 아마도 최근 업그레이드 되지 않은 기본 파일인듯 합니다. GeoIP 라는 이름으로 알려져 있습니다.

 

1. DB 구성(MSSQL 예제)

 

CSV를 MSSQL에서 import 합니다.

DB 구성은 아래의 구성에 , 구분자에 의해 합니다.

csv 중 한 레코드 -> 58.29.0.0,58.29.255.255,974979072,975044607,KR,Korea, Republic of

파일을 분석해 보면 시작IP, 종결IP, 시작IP 10진수 표현, 종결IP 10진수 표현, 국가코드 , 상세 등입니다.

 

2. 이제는 펑션을 하나 만듭니다. 엔드유저의 IP에 대해 비교를 위한 10진수 전환이 필요 합니다.

아마도 이부분이 핵심이 아닌가 합니다.

 

create function [dbo].[FN_IPConvert] (@ipaddr as varchar(50)) returns bigint as

begin

declare @c1 varchar(50) , @c2 varchar(50) , @c3 varchar(50) , @c4 varchar(50)

declare @ipaddr1 varchar(50)

set @c1 = ''

set @c2 = ''

set @c3 = ''

set @c4 = ''

set @ipaddr1 = ''

select @c1 = substring(@ipaddr,0,Charindex('.',@ipaddr,0))

select @ipaddr1 = replace(@ipaddr,@c1+'.','')

select @c2 = substring(@ipaddr1,0,Charindex('.',@ipaddr1,0))

select @ipaddr1 = replace(@ipaddr1,@c2+'.','')

select @c3 = substring(@ipaddr1,0,Charindex('.',@ipaddr1,0))

select @c4 = replace(@ipaddr1,@c3+'.','')

return (cast(@c1 as bigint)*256*256*256)+(cast(@c2 as bigint)*256*256)+(cast(@c3 as int)*256)+(cast(@c4 as bigint))

end

ip 대역중 각 클래스 별로 256 의 3승,2승,1승,0승을 곱하여 모두 더합니다. 이렇게 하면 256 진법상의 주소를 10진수로 표현한것과 같습니다.

 

3. 비교 프로시져 작성

 

제법 레코드수가 많으므로 국가코드에 인덱스를 지정한 후 'KR' 비교를 합니다. 본 예제는 한국 IP 가 아니면 액션을 취할 목적 이므로 한국을 비교 합니다. etc1,etc2,NN 은 각각 시작IP 10진수 표현, 종결IP 10진수 표현, 국가코드 필드 입니다. 즉, IP 주소는 비교상 난해 하므로 10진수로 변환하여 그 대역에 있는지를 비교하는 절차 입니다.

 

create procedure [dbo].[USP_iparea](@ipaddress as varchar(20)) as

select * from iparea where FN_IPConvert(@ipaddress) between 

cast (etc1 as bigint) and cast (etc2 as bigint) and NN = 'KR'

MS-SQL / SQL Server

GeoIP, IP2Location등의 비교를 위해서 IP주소를 숫자(bigint)형식으로 변경


declare @result bigint

declare @fdIP varchar(15)

set @fdIP = '122.208.20.106'


DECLARE @fdIP1 bigint, @fdIP2 bigint, @fdIP3 bigint, @fdIP4 bigint


SELECT @fdIP1 = PARSENAME(@fdIP, 4)

SELECT @fdIP2 = PARSENAME(@fdIP, 3)

SELECT @fdIP3 = PARSENAME(@fdIP, 2)

SELECT @fdIP4 = PARSENAME(@fdIP, 1)


SET @result = @fdIP1*(256*256*256)+@fdIP2*(256*256)+@fdIP3*(256)+@fdIP4

 

SELECT @result 

 

 

 

--- 혹은 이렇게 ---------------------------------------------------------------------------

 

SELECT 

IpAddr

, CONVERT(BIGINT,(PARSENAME(IpAddr, 4)))*(256*256*256)

+ CONVERT(BIGINT,(PARSENAME(IpAddr, 3)))*(256*256)

+ CONVERT(BIGINT,(PARSENAME(IpAddr, 2)))*(256)

+ CONVERT(BIGINT,(PARSENAME(IpAddr, 1))) AS IpNumber1)) AS IpNumber

FROM 

(

 SELECT '122.208.20.106' IpAddr

) A

MS-SQL / SQL Server

모든 사용자 테이블을 파일로 익스포트하기


데이터베이스에 있는 모든 사용자 테이블을 익스포트해서 파일로 만드는 스크립트로 각각의 테이블마다 하나씩 파일을 생성한다.

if exists(select name from sysobjects where name = 'BCP_out_AllTables')

  begin

    drop procedure BCP_out_AllTables

  end

GO

CREATE PROCEDURE BCP_out_AllTables

 @dbname   varchar(30),

 @path   varchar(50) = "C:\Temp"

AS

SET NOCOUNT ON

DECLARE @tablename   varchar(30)

DECLARE @cmdline  varchar(125)

DECLARE @ssql                   varchar(255)

DECLARE @tabcount  smallint

SELECT @tabcount = 0

EXEC ('USE ' + @dbname)

create table #dumptables ([name] varchar(255))

set @ssql = 'insert into #dumptables SELECT [name] from ' + @dbname + '..sysobjects where type = ''U'''

exec (@ssql)

DECLARE cnames  CURSOR FOR 

select [name] from #dumptables

OPEN cnames

FETCH NEXT FROM cnames INTO @tablename

WHILE (@@fetch_status <> -1)

BEGIN

 IF (@@fetch_status = -2)

 BEGIN

  FETCH NEXT FROM cnames INTO @tablename

  CONTINUE

 END

        

 PRINT 'Exporting table: ' + @tablename

 /* build commandline */

 -- Add "-S<servername>" for a remoteserver, terminator used = ~ (tilde), specify terminator after '-t', '-T' is used for trusted connection, 

 -- use -U<username> -P<password> for standard security

 SELECT @cmdline = 'bcp ' + @dbname + '..' + @tablename + ' out ' + @path + '\' + @tablename + '.dat -c -t~  -T'

 EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT

 SELECT @tabcount = @tabcount + 1

 FETCH NEXT FROM cnames INTO @tablename

END

DEALLOCATE cnames

/* Print usermessage */

SELECT CONVERT(varchar(10),@tabcount) + ' tables from database '+ @dbname + ' exported to ' + @path

GO

sp_help "BCP_out_AllTables"

GO

+ Recent posts