블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
  • total
  • today
  • yesterday
2010. 11. 3. 00:11 SQL SERVER

############ SQL2005버전확인 ################################
SELECT
 @@VERSION AS [@@VERSION]
,  SERVERPROPERTY('ProductVersion') AS ProductVersion
,  SERVERPROPERTY('ProductLevel') AS ProductLevel
GO


############ 데이타베이스특성보기 ################################
select DATABASEPROPERTYEX('ting' , 'IsQuotedIdentifiersEnabled' )
exec sp_MSforeachtable "exec sp_spaceused '?'" -->각 테이블별 용량

############ 모니터링할 것들 ################################
DISK I/O
Processor
Memory
User connections
Network


############ 백업히스토리삭제 #############################
Declare  @PurgeDate datetime
set @PurgeDate = DATEADD(d, -1, CURRENT_TIMESTAMP)
 EXEC msdb..sp_delete_backuphistory @PurgeDate


############ MDF, LDF 복구 ################################
EXEC sp_attach_db AAA,
   'E:\SQLDAT\AAA.MDF',
   'E:\SQLDAT\AAA.LDF'


EXEC sp_attach_db AAA,
   'E:\AAA.MDF',
  'E:\AAA.ldf'


############백업한DB 리스토어################################
RESTORE FILELISTONLY FROM disk = 'c:\AAA.SQLDAT'

RESTORE DATABASE AAA
from disk = 'e:\AAA.SQLDAT'
with MOVE 'AAA_Data' to 'c:\AAA.MDF',
MOVE 'AAA_Log' to 'c:\AAA.LDF'


############로그전달복원################################
--디비복원
RESTORE DATABASE "AAA"
WITH NORECOVERY
--로그복원
restore log "AAA"
 FROM DISK =  'c:\aaa\AAA.TRN' 
with norecovery

--로그복원라스트
restore log "AAA"
 FROM DISK =  'c:\aaa\AAA.TRN' 
with recovery


############ 프로시저 제거와등록 ################################
--제거
sp_dropextendedproc 'xp_cmdshell'

--이름 등록
sp_addextendedproc 'xp_cmdshell' ,  'xplog70.dll'
sp_addextendedproc 'xp_regread' ,  'xpstar.dll'
sp_addextendedproc xp_dirtree,'xpstar.dll' -->맞는지 아닌지 한번 확인해볼것

--보안상 회수해야할 권한
--master database
--확장된 저장형 프로시저 퍼미션 삭제
REVOKE EXECUTE ON xp_regread FROM public
REVOKE EXECUTE ON xp_dirtree FROM public
REVOKE EXECUTE ON xp_instance_regread FROM public
--sp_runwebtask 접근을 통한 web task 실행 권한 획득 방지
REVOKE EXECUTE ON dbo.sp_runwebtask FROM public

############### 단일사용자모드로 접속 #############################
alter database AAA set single_user with ROLLBACK IMMEDIATE
sp_renamedb AAA, AAA_OLD
alter database AAA_OLD set multi_user with ROLLBACK IMMEDIATE
############### 계정만들기 프로시저 #############################
--sa계정으로 해야함
use community2

//로그인만들기 아이디, 암호, 디폴트 디비
sp_addlogin 아이디, 암호, 디폴트 디비

//사용자만들기
create user 사용자 from LOgin 로그인이름

//sysadmin권한을 갖도록 한다.
sp_addsrvrolemember '계정', 'sysadmin'

//db_owner 권한을 준다.
sp_addrolemember  'db_owner', '디비'


--현재 부여한 역할 삭제
sp_droprolemember  'db_owner', 'testUser'

--현재 user삭제
sp_dropuser 'testUser'

--현재 login 삭제
sp_droplogin 'testUser'


############### 속도빠른카운트 #############################
SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('테이블이름') AND indid < 2

 

############### 모든테이블카운트하기 #############################
sp_MSforeachtable 'select count(*) "?" from ?'

############### 2. 스토어드 프로시져에 대한 파라미터로 테이블 이름을 사용하는 방법
Create proc dbcc_table
    @tabname varchar(30)
    as
    BEGIN
    Exec ("dbcc checktable (" + @tabname + ")")
    END
    go
 
############### 컬럼의선택성체크(낮을수록좋다. #############################
SELECT 1.0 / COUNT(DISTINCT 컬럼명) * 100 FROM 테이블

############### I/O많이 일으키는 순으로 데이타베이스 정리. #############################
WITH DBIO AS
(
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
)
SELECT db, file_type,
CAST(1. * io / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
CAST(io_stall / 1000. AS DECIMAL(12, 2)) AS io_stall_s,
CAST(100. * io_stall / SUM(io_stall) OVER()
AS DECIMAL(10, 2)) AS io_stall_pct,
ROW_NUMBER() OVER(ORDER BY io_stall DESC) AS rn
FROM DBIO
ORDER BY io_stall DESC;
 
############### 숫자칼럼에서 이빨빠진 최초값 구하기 #############################
SELECT
CASE
WHEN NOT EXISTS(SELECT * FROM dbo.T1 WHERE keycol = 1) THEN 1
ELSE (SELECT MIN(keycol + 1)
FROM dbo.T1 AS A
WHERE NOT EXISTS
(SELECT *
FROM dbo.T1 AS B
WHERE B.keycol = A.keycol + 1))
END;

############### 2005에서2000으로 LinkedServer #############################
2000서비스 팩내의 instcat.sql 스크립트 실행

############### LinkedServer 만들기 #############################
EXEC sp_addlinkedserver @server='DEVSERVER',@srvproduct='', -- 연결이름.
@provider='SQLOLEDB', @datasrc='목적지IP'
EXEC sp_addlinkedsrvlogin 'DEVSERVER', 'false', NULL, '아이디', '암호'

############### lock정보 보기 #############################
select request_session_id As spid,
  resource_type  as restype,
  resource_database_id as dbid,
  resource_description as res,
  resource_associated_entity_id as resid,  
  request_mode as mode,
  request_status as status
 from sys.dm_tran_locks

############### Num값으로 증가하는 테이블 만들기 #############################
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 10;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
############################BCP로 데이타 다루기#################################
D:\>bcp "select * from DB이름.dbo.TABLE" queryout d:\test.txt -c -S
서버이름 -U 아이디 -P 암호
--쿼리분석기에서 테이블로 올리기
BULK INSERT mytemp
FROM "d:\test.txt"
WITH (FIRSTROW = 1,ROWTERMINATOR ='\n');
--bcp로 올리기
bcp tempdb.dbo.mytemp in d:\test.txt -c -S 서버이름 -U 아이디-P 암호
bcp "etempdb.dbo.mytemp" in "파일이름" -c -q -S"서버이름" -U"아이디"
-P"암호"
-S : 서버이름을 적습니다.
-U : SQL 서버인증으로 접속할시 계정명을 적습니다.
-P : SQL 서버인증으로 접속할시 암호를 적습니다.
-T : NT인증을 사용하여 접속합니다. 이를 사용하면 -U, -P는 사용하지 않습니다.
-f : 포맷파일의 위치를 지정합니다.
-t : 필드터미네이터라고 부르며 각 컬럼들간의 구분자를 지정합니다.
-r : 로우터미네이더라고 부르며 각 행을 구분짓는 구분자를 지정합니다.
-m : BCP작업이 취소되는 최대 에러갯수를 지정합니다.
-F : 전송이 시작될 첫행의 지시합니다. 기본은 처음부터 입니다.
-L : 전송의 마지막이될 행을 지시합니다. 기본은 맨 마지막 행입니다.
-c : 데이터들을 모두 문자형으로 취급하여 전송합니다.

##############################2005랜덤 데이타 뽑기##############################
단, 테이블 자체를 저장하는 페이지 단위로 추출하기 때문에 대량을 레코드가 있을 시에만 효과적
select * from  테이블 tablesample(0.2 percent)

##############################특정SPID가 날린 쿼리확인##############################
dbcc inputbuffer(SPID)

'SQL SERVER' 카테고리의 다른 글

좋은 stored procedure 작성법  (0) 2010.11.03
SQL SERVER 트리거  (0) 2010.11.03
SQL 실행계획이 다시 컴파일 되는 경우  (0) 2010.11.03
SQL2005 랭킹 함수  (0) 2010.11.03
Drived Table과 CTE(Common Table Express)비교  (0) 2010.11.03
posted by bedbmsguru