kimyongpil

tempdb 초기화

2012-02-01T17:55:00.000-08:00

backup log tempDB with no_log
DBCC SHRINKDATABASE(N'tempdb' )

다른 디렉토리로 변경
http://www.dator.co.kr/?document_srl=65617&tags=Database

Oracle 쿼리 로그

2011-11-09T16:18:00.001-08:00

select * from v_$sql where sql_text like '%table%'

TRIGGER일때 자동 증가값 조회

2011-08-20T03:56:00.000-07:00

GO
/****** 개체: Trigger [dbo].[tr_del] 스크립트 날짜: 08/20/2011 19:52:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
Create TRIGGER [dbo].[tr_del]
ON [dbo].[LicIssueInfo]
FOR INSERT
AS
DECLARE @idx int
DECLARE @strSql nvarchar(4000)
DECLARE @subSql nvarchar(4000)

SELECT @idx = [idx]
FROM inserted
SET @strSql = N'CREATE TABLE [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] (
[idx] [int] NOT NULL ,
[pckidx] [int] NULL ,
[userID] [varchar] (50) COLLATE Korean_Wansung_CI_AS NULL ,
[userIP] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
[userMac] [varchar] (15) COLLATE Korean_Wansung_CI_AS NULL ,
[userLoc] [char] (2) COLLATE Korean_Wansung_CI_AS NULL ,
[issueDate] [datetime] NULL
) ON [PRIMARY]'
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN
SET @subSql = N'INSERT INTO [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] SELECT * from LicIssueInfo WITH (NOLOCK) where idx=' + convert(char,@idx)
EXEC sp_executesql @subSql
delete from [dbo].[LicIssueInfo] where idx = @idx - 300000
END

ELSE

BEGIN
EXEC sp_executesql @strSql
SET @subSql = N'INSERT INTO [dbo].[LicIssueInfo_backup_'+ replace(convert(char(10),getdate(),120),'-','') +'] SELECT * from LicIssueInfo WITH (NOLOCK) where idx=' + convert(char,@idx)
EXEC sp_executesql @subSql
delete from [dbo].[LicIssueInfo] where idx = @idx - 300000
END
END

insert 자동 증가 값 조회

2011-08-20T03:48:00.000-07:00

DECLARE @strSql nvarchar(4000)
DECLARE @strSql2 nvarchar(4000)

SET @strSql = N'CREATE TABLE [dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode +'] (
[Idx] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[uid] int
) ON [PRIMARY]'

BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
---테이블이 존재하면,
BEGIN
---수험번호 증가
--EXEC sp_executesql 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') + '_' + @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
--select @idx = @@Identity
set @strSql2 = 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
EXEC sp_executesql @strSql2
select @idx = @@Identity

END
ELSE
---테이블이 없다면,
BEGIN
---테이블 생성
EXEC sp_executesql @strSql
---수험 번호 증가
--EXEC sp_executesql 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
--select @idx = @@Identity
set @strSql2 = 'INSERT INTO [MBCRecruit].[dbo].[T_Recruit_Apply_'+ replace(@DivCode,'-','_') +'_'+ @DutyCode + '] ([uid]) VALUES ('+ convert(varchar,@uid) + ')'
EXEC sp_executesql @strSql2
select @idx = 1
END
END

db lock 많을때

2011-04-20T23:27:00.000-07:00

그림과 같이 sp_lock 를 실행하세요 ..

5가지 정보를 보시면 되는데요

spid / db_id / object_id / mode / status

spid 는 할당된 프로세스 ID 이구요 ( spid가 여러개일 경우 특정 처리임계치를 넘어서 병열처리로 넘어갔다고 보면 됩니다. 이놈들이 현재 리소스를 잡아먹고 있다고 보시면 됩니다. )

dbid 는 어떤 db로 접근했는가

object_id 는 어떤 객체를 건들이고 있는가 .. table ? index ? sp ??

mode 는 s / is / x /ix 그외에 여러가지가 있는데요

s는 select 처리시 발생되는 공유락 정도로 보시면 되구요 ( 100% 정확한건 아니구요 )

x 는 트랜잭션 처리중 발생하는것이거나 데이터가 커져서 데이터 페이지 분할이나 확장으로 인한 객체 단위의 배타락일수 있구요 ( 100% 아니구요) 배타락의 경우 다음 프로세스가 락을 유발하는 경우 wait 상태로 빠트려서 현재 처리하는 작업이 끝날때까지 다른 작업은 계속 wait 이구요

상황은 여러가지와 엮기는데요 .. 암튼 ..

가장 심플한것은 spid가 심하게 겹치는 애들을 살펴 보시면 되요

sp_who2 79 << 이런식으로 ..

kill 79

상위 명령을 내리시기 전에 .. 고민해보셔야 하는게 ... select 쿼리라면 ... 사용자가 혹은 배치가 돌다가 끝나도 문제는 안되는데 ..

트랜잭션일 경우 문제가 될수 있다는거죠 .. 그래서 kill 시키기 전에 어느 db 인지 어떤 객체 인지 그리고 sp_who2 를 통해서

1. 어떤 웹서버에서 요청이 왔는지

2. 스케줄이 돌았는지

3. 미친 개발자가 있는지?

대충 sp_who2 에 나오는 hostname 으로 해당 PC 나 서버를 파악하고 application 의 정보를 보고 IIS 인지 SQL 서버인지도 판단을 하구요

select 쿼리 였는지 트랜잭션 인지 ... 등 정보들을 파악하고 kill 시켰을때의 영향에 대해서 보셔야 하구요 ...

대충 여기까지요 ^^;;

SQL 해당 글자의 Char ASCII값

2011-01-12T21:29:00.001-08:00

SET TEXTSIZE 0
-- Create variables for the character string and for the current
-- position in the string.
DECLARE @position int, @string char(8)
-- Initialize the current position and the string variables.
SET @position = 1
SET @string = 'New Moon'
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT ASCII(SUBSTRING(@string, @position, 1)),
CHAR(ASCII(SUBSTRING(@string, @position, 1)))
SET @position = @position + 1
END
GO

DB 이름 변경

2011-01-04T00:52:00.000-08:00

데이타베이스 명령어인 renamedb를 사용하여 변경
alter database ADAM set single_user with rollback immediate
EXEC sp_renamedb 'ADAM', 'ADAM_20110104_old'
alter database ADAM_20110104_old set multi_user with rollback immediate

i-on CMS에서 Replace 관련

2010-12-28T21:34:00.001-08:00

로그 줄이기

2010-12-23T03:57:00.001-08:00

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 크기로 줄임)

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

2010-12-19T16:44:00.001-08:00

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

로그 백업 Query

2010-10-16T01:05:00.001-07:00

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

2010-10-16T01:04:00.001-07:00

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

2010-10-16T01:01:00.000-07:00

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 생성

2010-10-16T00:24:00.000-07:00

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

SQL 정보 관련 조회

2010-10-15T19:40:00.000-07:00

버전을 확인하는 방법

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

SELECT @@VERSION

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

DB 접근 Host session 의 용도 분류

2010-07-27T17:59:00.003-07:00

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

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

2010-07-27T17:59:00.001-07:00

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 수

2010-07-27T17:57:00.000-07:00

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

링크드 서버 연결 쿼리

2010-07-12T21:37:00.003-07:00

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

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

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

2010-07-12T21:37:00.001-07:00

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 로 될 것

.NET 다운로드

2010-06-09T22:05:00.000-07:00