kimyongpil: 2010

2010년 12월 28일 화요일

i-on CMS에서 Replace 관련

2010년 12월 23일 목요일

로그 줄이기

0. 로그 백업

declare @path varchar(1000)
set @path= 'E:\DBBackup\iCashDB_' + replace(convert(varchar(10),getdate(),121),'-','') + '.trn'
BACKUP LOG iCashDB TO DISK = @path

1. 로그 파일 크기 확인

DBCC SQLPERF(LOGSPACE)

2. 로그 데이터 지우기

BACKUP LOG DataBaseName WITH NO_LOG

3. 로그 파일 크기 줄이기

DBCC SHRINKFILE(DataBaseName_LOG, 1)

(1 : 1MB 크기로 줄임)

2010년 12월 19일 일요일

SP 수정 날짜, 생성 날짜 조회

SELECT name , create_date,modify_date FROM sys.objects
WHERE type = 'P' and
name in(
'www@USP_ProgramContentList_ByProgramCode',
'www@USP_ProgramContentListDetail_ByProgramCode'
)

2010년 10월 16일 토요일

로그 백업 Query

DECLARE CurQuery CURSOR
FOR

select name from dbo.sysdatabases
where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and
convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and
convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and
convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'
DECLARE @name varchar(100)

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
declare @dir nvarchar(200)
set @dir = N'e:\DBBackUP\' + @name+ '\' + @name + '_' + + convert(nvarchar(20), getDate(), 112) + replace(substring(convert(nvarchar(20), getDate(), 114), 1,5),':','') + '.lg'

backup log @name to DISK = @dir
exec [USP_RestoreQuery] @dir ,@name

PRINT @dir

FETCH NEXT
FROM CurQuery
INTO @name

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery

증분 백업 Query

CREATE TABLE #Tmp(
Query VARCHAR(4000)
)

DECLARE CurQuery CURSOR
FOR

select name from dbo.sysdatabases
where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and
convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and
convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and
convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'
DECLARE @name varchar(100)

DECLARE @ndir1 varchar(1000)
DECLARE @ndir2 varchar(1000)
set @ndir1= convert(nvarchar(20), getDate(), 112)
set @ndir2= replace(substring(convert(nvarchar(20), getDate(), 114), 1,5),':','')

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
declare @dir nvarchar(200)
set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + @ndir1 +@ndir2+ '.dif'

backup database @name to DISK = @dir with differential
exec [USP_RestoreQuery] @dir ,@name

INSERT INTO #Tmp
(Query)
VALUES
( ' [USP_RestoreQuery] ' + '''' + @dir + '''' + ',' + '''' + @name + '''' )

PRINT @dir

FETCH NEXT
FROM CurQuery
INTO @name

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery

DECLARE CurQuery2 CURSOR
FOR
select Query from #Tmp
DECLARE @Q nvarchar(4000)

OPEN CurQuery2

FETCH NEXT FROM CurQuery2 INTO @Q

WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @Q
EXEC sp_executesql @Q
print ' '
print ' '
print ' '

FETCH NEXT
FROM CurQuery2
INTO @Q

END --end of while

CLOSE CurQuery2

DEALLOCATE CurQuery2

TRUNCATE TABLE #Tmp
drop table #Tmp

풀 백업 Query

CREATE TABLE #Tmp(
Query VARCHAR(4000)
)

DECLARE CurQuery CURSOR
FOR

select name from dbo.sysdatabases
where name not in ('master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind') and
convert(sysname,DatabasePropertyEx(name,'Status')) ='ONLINE' and
convert(sysname,DatabasePropertyEx(name,'Updateability')) <>'READ_ONLY' and
convert(sysname,DatabasePropertyEx(name,'Recovery')) <>'SIMPLE'

DECLARE @name varchar(100)

DECLARE @ndir1 varchar(1000)
DECLARE @ndir2 varchar(1000)
set @ndir1= convert(nvarchar(20), getDate(), 112)
set @ndir2= replace(substring(convert(nvarchar(20), getDate(), 114), 1,5),':','')

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
declare @dir nvarchar(200)
set @dir = N'D:\DBBackUP\' + @name+ '\' + @name + '_' + + @ndir1 +@ndir2+ '.dmp'

backup database @name to DISK = @dir
--exec [USP_RestoreQuery] @dir ,@name

--PRINT 'exec [USP_RestoreQuery] ' + '''' + @dir + '''' + ',' + '''' + @name + ''''

INSERT INTO #Tmp
(Query)
VALUES
( ' [USP_RestoreQuery] ' + '''' + @dir + '''' + ',' + '''' + @name + '''' )

FETCH NEXT
FROM CurQuery
INTO @name

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery

DECLARE CurQuery2 CURSOR
FOR
select Query from #Tmp
DECLARE @Q nvarchar(4000)

OPEN CurQuery2

FETCH NEXT FROM CurQuery2 INTO @Q

WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @Q
EXEC sp_executesql @Q
print ' '
print ' '
print ' '

FETCH NEXT
FROM CurQuery2
INTO @Q

END --end of while

CLOSE CurQuery2

DEALLOCATE CurQuery2

TRUNCATE TABLE #Tmp
drop table #Tmp

백업 파일 자동 복원 Query 생성

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--[USP_RestoreQuery] 'E:\db 백업\완료121.254.134.170\DBBACKUP\TvianImnews\TvianImnews_201010142359.dmp' ,'MyPageDb'

ALTER PROCEDURE [USP_RestoreQuery]
@path varchar(1000),
@dbname varchar(1000)
AS
SET NOCOUNT ON
BEGIN

--declare @path varchar(1000)
--declare @dbname varchar(1000)
declare @Query varchar(1000)

--set @path = 'E:\db 백업\211.233.27.90\dbbackup\MyPageDb\MyPageDb_201010142335.dmp'
--set @path = 'E:\db 백업\완료121.254.134.170\DBBACKUP\TvianImnews\TvianImnews_1.dif'
--set @dbname = 'MyPageDb'

CREATE TABLE #dbfiles(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size numeric(20,0)
,MaxSize numeric(20,0) /*

,FileId INT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes INT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly INT
,IsPresent INT */
)
set @Query = 'Restore FileListOnly from Disk =''' + @path + ''''
INSERT #dbfiles
EXEC(@Query)

DECLARE CurQuery CURSOR
FOR

SELECT LogicalName, PhysicalName --, Type
FROM #dbfiles
--select ';' as LogiclName,'fdsf' as physicalName

DECLARE @LogiclName varchar(1000)
DECLARE @physicalName varchar(1000)

OPEN CurQuery

FETCH NEXT FROM CurQuery INTO @LogiclName,@physicalName

if right(@path,3) <>'dif'
BEGIN
print 'RESTORE DATABASE '+ @dbname +' FROM '
print 'DISK = ''' + @path +''' WITH '
END
WHILE @@FETCH_STATUS = 0
BEGIN
if right(@path,3) <>'dif'
BEGIN
PRINT 'MOVE ''' + @LogiclName + '''' + ' To '''+ @physicalName +''','

END

FETCH NEXT
FROM CurQuery
INTO @LogiclName,@physicalName

END --end of while

CLOSE CurQuery

DEALLOCATE CurQuery
if right(@path,3) <>'dif'
BEGIN
PRINT 'FILE =1, noRECOVERY , REPLACE'
END

if right(@path,3) ='dif'
BEGIN
PRINT 'RESTORE DATABASE '+ @dbname +' FROM DISK = '''+ @physicalName +''' WITH RECOVERY'
PRINT 'alter database '+ @dbname +' set multi_user with rollback immediate'
End

print ' '
print ' '
print ' '
print ' '

TRUNCATE TABLE #dbfiles
drop table #dbfiles

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

2010년 10월 15일 금요일

SQL 정보 관련 조회

버전을 확인하는 방법

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SELECT @@VERSION

http://support.microsoft.com/kb/321185/ko

2010년 7월 27일 화요일

DB 접근 Host session 의 용도 분류

select count(lastwaittype),lastwaittype from master.dbo.sysprocesses where hostname='MYWEB102' group by lastwaittypeorder by lastwaittypeuse article_count

DB 접근 Host session 수(DB 계정별)

select hostname,program_name,loginame, count(hostname) from master.dbo.sysprocesses where hostname<>''group by hostname,program_name,loginameorder by count(hostname) desc

DB 접근 Host session 수

select hostname,program_name,count(hostname) from master.dbo.sysprocesses where hostname<>''group by hostname,program_name order by count(hostname) desc

2010년 7월 12일 월요일

링크드 서버 연결 쿼리

sp_addlinkedserver N'리스트 서버 별명', ' ', N'SQLOLEDB', N'연결서버 아이피'
go
sp_addlinkedsrvlogin N' 리스트 서버 별명', false, N'연결서버 계정', N'연결서버 계정', N'연결 서버 페스워드'
go

select top 1 * from 링크드별명.DB명.dbo또는계정.테이블명

Linked Server 저장프로시져 Job 호출시 에러 발생

Linked Server 저장프로시져 Job 호출시 에러 발생

[작업 환경]
Windows 2000 Advanced Server
SQL Server 2000 Standard Edition (Linked Server 구성)

[세부사항]
Linked Server로 구성된 2-System(A & B)에서 A System에 작업(Job) 스캐쥴을 생성 상대(B-system)에 DATA를 분석하여
본 서버에 DataBase를 업데이트
해당 작업 실행시
==================================================================================
SQL Server Scheduled Job 'aaa' (0x27C5AB0F8FAF0247BA561A9762700AEA) - Status: 실패 - Invoked on: 2005-12-01 15:09:01 - Message: 작업이 실패했습니다. 작업이 사용자 sa에 의해 호출되었습니다. 실행할 마지막 단계는 단계 1(ddd)입니다.
==================================================================================
이벤트 에러 발생

[문제 발생 원인]
상기 오류 구문만을 분석해 보면 구성된 Job을 실행함에 있어 관련 계정이 충분한 권한을 가지지 못한 것에서 발생한 메시지로
Job Scheduling은 SQLSERVERAGENT 서비스의 인증을 통해 타 시스템에 해당 쿼리를 실행하게 됨으로 두 시스템간의 인증문제로 유추.
최초에는 RPC 상속 문제라고 추측 관련 Stored Procedure를 수정하여 Linked 서버 Data를 가져오는 쿼리 부분을 B System에 작성하여 A System에서
OPENQUERY로 실행하려 했으나 Job 오류 기록을 세부적으로 살펴본 결과 SQLAgent Service의 계정 권한 문제로 판단 .
SQL 2000 서버에서 작업(Job)을 수행하기 위해서는, system admin role에 SQL Agent 계정이 포함되어 있어야 하나 고객님께 문의 결과 SQLAgent가
“Local System Account"로 수행되고 있으므로 해당 권한이 없었습니다.
Winodws 2000 계열에서 Local System Account는 System-Level의 서비스를 구동하기 위한 것으로 사용자 응용프로그램(여기서는 SQL의 Job Service)를
구동하는데에는 적절하지 않은 계정입니다.

[조치 사항]
SQLSERVERAGENT Service의 계정을 Local System => Administrator 계정으로 변경
서비스 재시작 후 작업 정상적으로 구동

CF)
1. SQLSERVERAGENT Service 가 Administrator 로 서비스 시작
2. 링크드 계정에 Administrator 계정 추가 될 것
3. Job 계정이 sa 로 될 것

kimyongpil