'SQL Server 2005'에 해당되는 글 50건

  1. 004_01.sp_sqlperf SQL SERVER 문서화되지 않은 상태 모니터링
  2. admin.sp_checkDBsize DB SIZE check
  3. admin.003.ap_TranMiLogBackup 분단위 트랜잭션 백업
  4. admin.002.ap_TranLogBackup
  5. 백업
  6. SQL Sample Database 등록하기
  7. Data 저장과 관련된 3가지 Process
  8. 안정적인 데이터베이스 구축과 장애복구 전략
  9. SQL 책 추천
  10. Why you want to be restrictive with shrink of database files


---------------------------------------------------------------------------------
-- 004_01.sp_sqlperf  SQL SERVER  문서화되지 않은 상태 모니터링
---------------------------------------------------------------------------------
-- 작성자 : 추숙
-- 수정자 :
-- 마지막 수정 내용
--  1. NULL :
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_sqlperf') 
 BEGIN
  DROP  PROCEDURE sp_sqlperf
 END
GO
CREATE  PROCEDURE  dbo.sp_sqlperf 
 @aPerfType TINYINT = NULL -- all 
AS
 SET NOCOUNT ON
 
 IF @aPerfType = 1
  OR @aPerfType IS NULL
 BEGIN
  SELECT '01. data about the transaction log for all of the databases on the SQL Server, including Database Name, Log Size (MB), Log Space Used (%), and Status.'
  DBCC SQLPERF (LOGSPACE);
 END
 IF @aPerfType = 2
  OR @aPerfType IS NULL
 BEGIN
  SELECT '02. data about SQL Server thread management.'
  DBCC SQLPERF (UMSSTATS);
 END

 IF @aPerfType = 3
  OR @aPerfType IS NULL
 BEGIN
  SELECT '03. data about wait types for SQL Server resources'
  DBCC SQLPERF (WAITSTATS);
 END

 IF @aPerfType = 4
  OR @aPerfType IS NULL
 BEGIN
  SELECT '04. data about outstanding SQL Server reads and writes'
  DBCC SQLPERF (RASTATS);
 END

 IF @aPerfType = 5
  OR @aPerfType IS NULL
 BEGIN
  SELECT '05. data about I/O, CPU, and memory usage per SQL Server thread. [7.0, 2000] Updated 3-20-2006.'
  DBCC SQLPERF (THREADS)
 END
 IF @aPerfType = 6
  OR @aPerfType IS NULL
 BEGIN
  SELECT '06. Note, this example was run on a 4 CPU server. There is 1 Scheduler ID per available CPU'
  DBCC SQLPERF (UMSSTATS);
 END
 
GO
-- TEST QUERY
exec dbo.sp_sqlperf -- all
exec dbo.sp_sqlperf 1
exec dbo.sp_sqlperf 2
exec dbo.sp_sqlperf 3
exec dbo.sp_sqlperf 4
exec dbo.sp_sqlperf 5
exec dbo.sp_sqlperf 6


---------------------------------------------------------------------------------
-- DB SIZE CHECK
---------------------------------------------------------------------------------
-- 작성자 : 김민석
-- 수정자 : 추숙
-- 마지막 수정 내용
--  1. 수정내용 없음 ㅜ.ㅜ
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_checkDBsize') 
 BEGIN
  DROP  PROCEDURE sp_checkDBsize
 END
GO
CREATE  PROCEDURE  dbo.sp_checkDBsize 
 @dbname sysname = null
AS
 SET NOCOUNT ON

 -- DB명 존재여부 체크
 IF @dbname IS NOT NULL
  AND @dbname NOT IN ( SELECT name
        FROM sysdatabases )
 BEGIN
   raiserror('relax!... you''re just one step away from the results. please verify the database name is correct and try again. ', 16, 1)
   return (1)
 END

 if exists (select name from tempdb..sysobjects where name = 'DBsize ' and type = 'u' )
    drop table tempdb..DBsize

 if exists (select * from tempdb..sysobjects where name = '#sizeinfo' and type = 'u')
  drop table #sizeinfo

 create table #sizeinfo
 (
  db_name varchar(30) not null primary key clustered ,
  total dec (7, 1) null,
  data dec (7, 1) null,
  data_used dec (7, 1) null,
  [data (%)] dec (7, 1) null,
  data_free dec (7, 1) null,
  [data_free (%)] dec (7, 1) null,
  log dec (7, 1) null,
  log_used dec (7, 1) null,
  [log (%)] dec (7, 1) null,
  log_free dec (7, 1) null,
  [log_free (%)] dec (7, 1) null,
  status dec (7, 1)  null
 )


 create table tempdb..DBsize
 (
  db_name varchar(30),
  data dec (7, 1) null,
  data_used dec (7, 1) null,
  [data (%)] dec (7, 1) null,
  data_free dec (7, 1) null,
  [data_free (%)] dec (7, 1) null,
  log dec (7, 1) null,
  log_used dec (7, 1) null,
  [log (%)] dec (7, 1) null,
  log_free dec (7, 1) null,
  [log_free (%)] dec (7, 1)  null
 )


 insert #sizeinfo ( db_name, log, [log (%)], status )
 exec ('dbcc sqlperf(logspace) with no_infomsgs')
 print '' print ''


 if @dbname is null
  declare dbname cursor for select name from sysdatabases order by name asc
 else if @dbname is not null
 begin
  delete from #sizeinfo where db_name <> @dbname
  declare dbname cursor for select name from sysdatabases where name = @dbname
 end

 open dbname
 fetch next from dbname into @dbname
 while @@fetch_status = 0
 begin     -- adding .0 at the end of interger to avoid divide by zero error
 
  exec ( ' use [' + @dbname + ']  declare @total dec(7, 1),
    @data dec (7, 1),
    @data_used dec (7, 1),
    @data_percent dec (7, 1),
    @data_free dec (7, 1),
    @data_free_percent dec (7, 1),
    @log dec (7, 1),
    @log_used dec (7, 1),
    @log_used_percent dec (7, 1),
    @log_free dec (7, 1),
    @log_free_percent dec (7, 1)   
    set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 / 1048576.0
    set @data = (select sum(size) from sysfiles where (status & 64 = 0)) * 8192.0 / 1048576.0
    set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255)) * 8192.0 / 1048576.0
    set @data_percent = (@data_used * 100.0 / @data)
    set @data_free = (@data - @data_used)
    set @data_free_percent = (@data_free * 100.0 / @data )
    set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')
    set @log_used_percent = (select [log (%)] from #sizeinfo where db_name = '''+@dbname+''')
    set @log_used = @log * @log_used_percent / 100.0
    set @log_free = @log - @log_used
    set @log_free_percent =  @log_free * 100.0 / @log
    update #sizeinfo set total = @total,
      data = @data ,
      data_used = @data_used,
      [data (%)] = @data_percent,
      data_free = @data_free,
      [data_free (%)] = @data_free_percent,
      log_used = @log_used,
      log_free = @log_free,
      [log_free (%)] = @log_free_percent
       where db_name = '''+@dbname+'''')
  fetch next from dbname into @dbname
 end
 close dbname
 deallocate dbname

 print '***  note: all numbers are in mb. if you see unreasonable or negative values, it is time to run dbcc updateusage.  ***'
 print ''

 --insert into tempdb..DBsize (db_name,  data, data_used, [data (%)], data_free, [data_free (%)], log, log_used, [log (%)], log_free, [log_free (%)])
 select @@servername servername, db_name, data, data_used, [data (%)], data_free, [data_free (%)], log, log_used, [log (%)], log_free, [log_free (%)]
 from #sizeinfo --order by db_name asc --compute sum(total)


 drop table #sizeinfo

GO

-- TEST QUERY
exec dbo.sp_checkDBsize   -- 모든 데이타베이스
exec dbo.sp_checkDBsize 'AdventureWorks' -- 특정 데이타베이스 사이즈를 측정 한다.



---------------------------------------------------------------------------------
-- 분단위  트랜잭션 백업 로그
---------------------------------------------------------------------------------
-- 작성자 : 차주언
-- 수정자 : 추숙
-- 마지막 수정 내용
--  1. 트랜잭션 파일 시간을 조정 가능하도록 변경
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_TranMiLogBackup') 
 BEGIN
  DROP  PROCEDURE ap_TranMiLogBackup
 END
GO
CREATE PROC [dbo].[ap_TranMiLogBackup]
 @DBNAME varchar(20),
 @physical_path varchar(100) = 'c:\Backup\TBackup\',
 @FullBackupHour TINYINT = 6 , -- full backup 시간 
 @DelLogHour INT = 48   -- (시간) 삭제할 이전의 DEVICE 생성일 비교
AS
 SET NOCOUNT ON
 
 -- 변수 선언
 DECLARE
  @query varchar(1000),
  @cur_hour tinyint,
  @cur_min tinyint,
  @del_hour tinyint,
  @del_min tinyint,
  @cur_device varchar(50),
  @del_device varchar(50),
  @description varchar(48),
  @physical_name  varchar(64) 

 DECLARE @aCnt INT,
   @aID INT,
   @aDeviceName VARCHAR(1000)

 DECLARE @aTemp TABLE (
  mID  INT   IDENTITY(1,1),
  mDate DATETIME,
  mDeviceName VARCHAR(1000)
 )
 
 SELECT @aCnt = 0, @aID = 0, @aDeviceName = ''  

 SET @cur_hour = datepart(hour,getdate())
 SET @cur_min = datepart(mi,getdate())
 
 -- Full Backup 시간은 제외함.
 IF ( @cur_hour = @FullBackupHour )
  Return
 
 -- 삭제할 로그 정보 
 INSERT INTO @aTemp( mDate, mDeviceName )
 SELECT
  mDate,
  name
 FROM (
  SELECT
   convert( datetime, mYY + '-' + mMM + '-'+ mDD + ' ' + mHH + ':' + mMT + ':00' ) mDate,
   name  
  FROM (
   select
    SUBSTRING(RIGHT(name, 13), 1, 4) mYY, -- 년
    SUBSTRING(RIGHT(name, 13), 5, 2) mMM, -- 월
    SUBSTRING(RIGHT(name, 13), 7, 2) mDD, -- 월

    SUBSTRING(RIGHT(name, 4),1,2) mHH, -- 시
    SUBSTRING(RIGHT(name, 4),3,2) mMT, -- 분
    name
   from master.dbo.sysdevices
   where name like '%tlog%'
    and ISNUMERIC(right(name, 4)) = 1 ) T1 ) T3
 WHERE mDate <= DATEADD(HH, -@DelLogHour, GETDATE()) 

 SELECT @aCnt = @@ROWCOUNT, @aID = 1 
  
 
 --현재 Device Setup
 SET @description = 'Log Minute Backup , ' + @DBNAME  + '  ' + CONVERT(CHAR(16),GETDATE(),20)   
 SET @cur_device = @DBNAME + '_tlog_' + CONVERT(char(8),getdate(),112) + '_'   

 
 IF @cur_hour < 10
  SET @cur_device = @cur_device + '0' + convert(char(1),@cur_hour)
 ELSE
  SET @cur_device = @cur_device + convert(char(2),@cur_hour)

 IF @cur_min < 10
  SET @cur_device = @cur_device + '0' + convert(char(1),@cur_min)  
 ELSE
  SET @cur_device = @cur_device + convert(char(2),@cur_min)

 SET @physical_name = @physical_path + @cur_device + '.tbak'

 --이전의 device를 삭제한다. 
 IF ( @aCnt >= 1 )
 BEGIN  
  WHILE ( @aCnt >= @aID )
  BEGIN
   
   SELECT
    @aDeviceName = mDeviceName 
   FROM @aTemp
   WHERE  mID = @aID

   EXEC sp_dropdevice @aDeviceName,'delfile'   

   SET @aID = @aID + 1
  END      
 END

 --dump device를 설정한다.
  IF ( select count(*) from master.dbo.sysdevices where name = rtrim(@cur_device) ) = 0  
   EXEC sp_addumpdevice 'disk' , @cur_device , @physical_name 

 --transaction log 를 백업받는다.
  SET @query = 'BACKUP LOG ' + @DBNAME + ' TO ' + @cur_device
  EXECUTE(@query)
GO

-- TEST QUERY
EXEC [ap_TranMiLogBackup] 'AdventureWorks', 'E:\DB_TEST\Backup\', DEFAULT, DEFAULT -- 48시간 이전
GO

EXEC [ap_TranMiLogBackup] 'AdventureWorks', 'E:\DB_TEST\Backup\', DEFAULT, 6 -- 6시간 이전 트랜잭션 파일 삭제
GO

 

'SQL Server 2005' 카테고리의 다른 글

004_01.sp_sqlperf SQL SERVER 문서화되지 않은 상태 모니터링  (0) 2008.10.13
admin.sp_checkDBsize DB SIZE check  (0) 2008.10.13
admin.002.ap_TranLogBackup  (0) 2008.10.07
백업  (0) 2008.10.06
SQL Sample Database 등록하기  (0) 2008.10.01

admin.002.ap_TranLogBackup


매일 매일 차주언 과장님의 피와 땀이 되는 스크립트 공유해 드립니다^^

부족한 부분 말씀해 주시와요^^

 

목적 : 트랜잭션 백업

내용 : 시간단위 트랜잭션 백업시 이용한다.

Script

USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_TranLogBackup') 
 BEGIN
  DROP  PROCEDURE ap_TranLogBackup
 END
GO
CREATE PROCEDURE dbo.ap_TranLogBackup
 @DBNAME varchar(20),
 @physical_path varchar(100) = 'd:\MSSQL\Backup\FBackup\',
 @FullBackupTm TINYINT = 6 -- full backup hour
AS
 SET NOCOUNT ON

 DECLARE @query varchar(1000),
   @cur_hour tinyint,
   @del_hour tinyint,
   @cur_device varchar(50),
   @del_device varchar(50),
   @description varchar(48),
   @physical_name  varchar(64) 
  

 SET @cur_hour = datepart(hour,getdate())
 SET @del_hour = datepart(hour,getdate())


 --Full Backup 시간은 제외함.
 IF @cur_hour = @FullBackupTm
  Return

 SET @cur_device = @DBNAME + '_tlog_' + CONVERT(char(8),getdate(),112) + '_'

 
 --48 시간 이전의 device를 삭제한다.
 SET @del_device = @DBNAME + '_tlog_' + CONVERT(char(8),dateadd(hour,-48,getdate()),112) + '_'
 SET @description = 'Log Hourly Backup , ' + @DBNAME  + '  ' + CONVERT(CHAR(16),GETDATE(),20) 
 
 IF @cur_hour < 10
  SET @cur_device = @cur_device + '0' + convert(char(1),@cur_hour)
 ELSE
  SET @cur_device = @cur_device + convert(char(2),@cur_hour)


 IF @del_hour < 10
  SET @del_device = @del_device + '0' + convert(char(1),@del_hour)
 ELSE
  SET @del_device = @del_device + convert(char(2),@del_hour)

 SET @physical_name = @physical_path + @cur_device + '.tbak'

 PRINT 'cur_device : ' + @cur_device
 PRINT 'del_device : ' + @del_device
 PRINT 'physical_name : ' + @physical_name
 
 IF ( select count(*) from master.dbo.sysdevices where name = rtrim(@del_device) ) = 1
  EXEC sp_dropdevice @del_device,'delfile'   

 IF ( select count(*) from master.dbo.sysdevices where name = rtrim(@cur_device) ) = 0  
  EXEC sp_addumpdevice 'disk' , @cur_device , @physical_name 


 SET @query = 'BACKUP LOG ' + @DBNAME + ' TO ' + @cur_device
 PRINT @query
 EXECUTE(@query)

GO

 

 

예제

EXEC DBO.ap_TranLogBackup 'AdventureWorks', 'E:\DB_TEST\Backup\FBackup\'

결과

AdventureWorks_tlog_20081007_22.tbak <-- 요렇게 파일이 생겼군요^^

 

 

현재 백업된 정보 보기

select * from master.dbo.sysdevices

'SQL Server 2005' 카테고리의 다른 글

admin.sp_checkDBsize DB SIZE check  (0) 2008.10.13
admin.003.ap_TranMiLogBackup 분단위 트랜잭션 백업  (0) 2008.10.13
백업  (0) 2008.10.06
SQL Sample Database 등록하기  (0) 2008.10.01
Data 저장과 관련된 3가지 Process  (0) 2008.09.29

백업

목적 : 전체 백업 관리

  • 백업 디바이스 지정 : 이전 버전의 SQL 경우, 백업을 실행하기 이전에 백업 장치에 대한 설정을 잡아주어야만 했다. 하지만 SQL2005에서는 이러한 설정을 잡아줄 필요가 없으며, 백업 장치를 정의해야 할 필요가 없다.  하지만, 백업 장치를 사용하면 시간이 지나도 백업 파일 이름과 장소가 늘 동일하게 유지하기 쉬운 방법을 제공한다.
  • 대용량의 백업 Data Transfer Options : 백업시 I/O 버퍼 사이즈 지정.
    가능 버젼 : SQL2000(sp4), SQL2005
    참고 링크 : http://msdn.microsoft.com/ko-kr/library/ms186865.aspx

 


SQL2000

---------------------------------------------------------------------------------
-- SQL 2000
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_db_backup') 
 BEGIN
  DROP  PROCEDURE ap_db_backup
 END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DBO.ap_db_backup
 @DBNAME  varCHAR(16),   
 @path  varchar(32) = 'E:\DB_TEST\Backup\', -- Backup PATH
 @gen  int = 7, -- 7 DAYS PASSED BACKUP FILE DROPS
 @IsTransferOption BIT = 0, -- default 0 : off 
 @bufferCount INT = 1,
 @maxtransfersize INT = 65536
AS
Begin   
 
  declare @dev_name varchar(64)   
  declare @dev_name_del varchar(64)   
  declare @physical_name  varchar(64)   
  declare @description varchar(64)   
   
  if ( select count(*) from master.dbo.sysdatabases where name = @dbname ) = 0    
  begin    
   select 'Database '' + @DBNAME + '' does not exist.'   
   return   
  end   
   
  select @description = @DBNAME  + ' , ' + CONVERT(CHAR(20),GETDATE())   
  select @dev_name = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() , 120 )   
  select @dev_name_del = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() - @gen , 120 )   
  select @physical_name = rtrim(upper(@PATH)) + rtrim(upper(@dev_name)) + '.BAK'   
  select @description = 'Daily Backup , ' + @DBNAME  + '   ' + CONVERT(CHAR(20),GETDATE())   
      
  if ( select count(*) from master.dbo.sysdevices where name = rtrim(@dev_name_del) ) > 0    
   EXEC sp_dropdevice @dev_name_del,'delfile'   
   
  if ( select count(*) from master.dbo.sysdevices where name = rtrim(@dev_name) ) = 0    
   EXEC sp_addumpdevice 'disk' , @dev_name , @physical_name 
   
  IF @IsTransferOption = 1
  BEGIN
   --Data Transfer Options ( BUFFERCOUNT, MAXTRANSFERSIZE )
   -- BUFFERCOUNT : 백업 작업에 사용되는 I/O 버퍼의 총 수를 지정합니다. 임의의 양의 정수를 지정할 수 있지만 버퍼 수가 많으면 Sqlservr.exe 프로세스의 부적절한 가상 주소 공간으로 인해 "메모리가 부족합니다"라는 오류가 발생할 수 있습니다.
   -- MAXTRANSFERSIZE : SQL Server와 백업 미디어 간에 사용되는 가장 큰 전송 단위(바이트)를 지정합니다. 가능한 값은 최대 4194304바이트(4MB)까지 65536바이트(64KB)의 배수입니다.
   -- 공식 : buffercount * maxtransfersize
   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , BUFFERCOUNT = @bufferCount
   , MAXTRANSFERSIZE = @maxtransfersize
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END
  ELSE
  BEGIN
   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END
 
end
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


SQL2005

USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ap_db_backup') 
 BEGIN
  DROP  PROCEDURE ap_db_backup
 END
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE DBO.ap_db_backup
 @DBNAME  varCHAR(16),   
 @path  varchar(32) = 'E:\DB_TEST\Backup\', -- Backup PATH
 @gen  int = 7, -- 7 DAYS PASSED BACKUP FILE DROPS
 @IsTransferOption BIT = 0, -- default 0 : off 
 @bufferCount INT = 1,
 @maxtransfersize INT = 65536
AS
Begin  
 
  declare @dev_name varchar(64)   
  declare @dev_name_del varchar(64)   
  declare @physical_name  varchar(64)   
  declare @description varchar(64)   
   
  if ( select count(*) from sys.sysdatabases where name = @dbname ) = 0    
  begin    
   select 'Database '' + @DBNAME + '' does not exist.'   
   return   
  end   
   
  select @description = @DBNAME  + ' , ' + CONVERT(CHAR(20),GETDATE())   
  select @dev_name = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() , 120 )   
  select @dev_name_del = @@SERVERNAME + '.' + @DBNAME + ' ' + convert(char(10),getdate() - @gen , 120 )   
  select @physical_name = rtrim(upper(@PATH)) + rtrim(upper(@dev_name)) + '.BAK'   
  select @description = 'Daily Backup , ' + @DBNAME  + '   ' + CONVERT(CHAR(20),GETDATE())   
      
  if ( select count(*) from sys.sysdevices where name = rtrim(@dev_name_del) ) > 0    
   EXEC sp_dropdevice @dev_name_del,'delfile'   
   
  if ( select count(*) from sys.sysdevices where name = rtrim(@dev_name) ) = 0    
   EXEC sp_addumpdevice 'disk' , @dev_name , @physical_name -- 2005 데이터베이스 엔진 의 인스턴스에 백업 장치를 추가합니다 
 
  -- backup option
  -- INIT :  미디어 헤더만 보존하고 모든 백업 세트를 덮어쓰도록 지정합니다
  -- NOSKIP :  BACKUP 문이 백업 세트를 덮어쓰기 전에 미디어에 있는 모든 백업 세트의 만료 날짜를 확인하도록 지정합니다.
  -- NOFORMAT : 이 백업 작업에 사용된 모든 볼륨에 미디어 헤더가 기록되지 않도록 지정합니다. 기본값입니다.
  IF @IsTransferOption = 1
  BEGIN
   --Data Transfer Options ( BUFFERCOUNT, MAXTRANSFERSIZE )
   -- BUFFERCOUNT : 백업 작업에 사용되는 I/O 버퍼의 총 수를 지정합니다. 임의의 양의 정수를 지정할 수 있지만 버퍼 수가 많으면 Sqlservr.exe 프로세스의 부적절한 가상 주소 공간으로 인해 "메모리가 부족합니다"라는 오류가 발생할 수 있습니다.
   -- MAXTRANSFERSIZE : SQL Server와 백업 미디어 간에 사용되는 가장 큰 전송 단위(바이트)를 지정합니다. 가능한 값은 최대 4194304바이트(4MB)까지 65536바이트(64KB)의 배수입니다.
   -- 공식 : buffercount * maxtransfersize
   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , BUFFERCOUNT = @bufferCount
   , MAXTRANSFERSIZE = @maxtransfersize
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END
  ELSE
  BEGIN
   BACKUP DATABASE @DBNAME
   TO @dev_name
   WITH INIT
   , NAME = @description
   , NOSKIP
   , NOFORMAT   
  END   
end
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- TEST QUERY
--exec dbo.ap_db_backup AdventureWorks, DEFAULT, DEFAULT, 1, DEFAULT, DEFAULT



자주 개인 PC에 테스트 하기 위해서 아래와 같은 수고스러운 작업해야 한다 ㅜ.ㅜ 기억을 위한 블로그에 업로드 하기...


1. SQL2000 용 샘플 디비를 SQL2005 서버에 등록하기
   다운로드 : http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en


use master
go


-- sample db attach
exec sp_attach_db NORTHWND, 'E:\DB_TEST\NORTHWND.MDF', 'E:\DB_TEST\NORTHWND.LDF'
go

-- current db check
sp_helpdb NORTHWND
go

-- NORTHWND sa로 사용자 변형
use NORTHWND
go

EXEC sp_changedbowner 'sa'
go

sp_helpdb
go

-- 호환 레벨 변경 SQL2005로 Upgrade
EXEC sp_dbcmptlevel NORTHWND, 90
go


 



 2. SQL2005용 샘플 디비를 SQL2005 서버에 등록하기
   
다운로드 : http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004  

   Source Code AdventureWorksDB.msi  설치하면 된다..


use master
go

exec sp_attach_db AdventureWorks, 'E:\DB_TEST\AdventureWorks_Data.mdf', 'E:\DB_TEST\AdventureWorks_Log.ldf'
go

sp_helpdb AdventureWorks




 

'SQL Server 2005' 카테고리의 다른 글

admin.002.ap_TranLogBackup  (0) 2008.10.07
백업  (0) 2008.10.06
Data 저장과 관련된 3가지 Process  (0) 2008.09.29
안정적인 데이터베이스 구축과 장애복구 전략  (0) 2008.09.09
SQL 책 추천  (0) 2008.08.26

  • Check Point
    비주기적으로 발생
    현재까지 진행된 모든 내용을 메모리에서 데이터 파일과 로그 파일에 기록하는 프로세스. 데이터베이스에서 많은 작업이 일어나면 체크 포인트가 발생하는 주기도 짧아지고, 데이터베이스에서 일어나는 작업이 많지 않으면 당연히 체크 포인트가 발생하는 주기도 길어진다.
  • Lazy Wirter
    Check Point와 동일하나 메모리 버퍼 캐쉬가 가득 찼을때 발생
  • Log Writer
    트랜잭션이 커밋되면, 자동으로 커밋된 트랜잭션에 대한 로그를 파일에 기록하는 프로세스

'SQL Server 2005' 카테고리의 다른 글

백업  (0) 2008.10.06
SQL Sample Database 등록하기  (0) 2008.10.01
안정적인 데이터베이스 구축과 장애복구 전략  (0) 2008.09.09
SQL 책 추천  (0) 2008.08.26
Why you want to be restrictive with shrink of database files  (0) 2008.08.22

SQL 책 추천

Enjoy Another Sandwich -- Kent Tegels

Yummy slices of SQL Server between slices of .NET and XML

Recommended readings: 2008 update

In class this week, I got asked a few times about the technical books I'd recommed for fulks interested in the data space. While I've had a list up on Amazon for while, I thought I'd post an updated list here.

http://sqlblog.com/blogs/kent_tegels/archive/2008/07/25/8044.aspx



SQL Server

  • A Developer's Guide to SQL Server 2005: Bob Beauchemin, Dan Sullivan
  • Accelerated SQL Server 2008: Rob Walters
  • Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform: Teo Lachev
  • Dissecting SQL Server Execution Plans: Grant Fritchey
  • Expert SQL Server 2005 Integration Services: Brian Knight, Erik Veerman
  • Inside Microsoft SQL Server 2005: Query Tuning and Optimization: Kalen Delaney, Sunil Agarwal, Craig Freedman, Ron Talmage, Adam Machanic
  • MCITP Developer: Microsoft SQL Server 2005 Database Sulutions Design: Victor Isakov
  • Pro SQL Server 2005 Service Broker: Klaus Aschenbrenner
  • Professional SQL Server 2005 XML: Scott Klein
  • SQL Server 2005 Practical Troubleshooting: The Database Engine: Ken Henderson
  • The Rational Guide to SQL Server 2005 Service Broker : Roger Wulter
Overview
Introduced in SQL Server 7.0 was the ability automatically grow and to shrink the physical size of database data and transaction log files. Auto grow of files doesn't happen before the file is full, it happens when new space is needed (like when an insert is performed), so the user will wait the time it takes to grow until the modification is completed.

Auto grow and shrink can be very useful under special circumstances, for example after archiving data to some other location. However, we often see DBA's doing shrink on a regular basis and the purpose of this article is to explain some of the downsides of shrink and what actually happens when you shrink a database file. Also, it is worth noticing that the auto grow functionality was mainly added so the dba wouldn't be paged in the middle of the night because the database is full. It was never intended to be a high performance feature or to replace the need to manage space usage, especially at the high end

Acknowledgements
I like to thank SQL Server MVP Mark Allison,
http://www.markallison.co.uk, who has provided valuable suggestions and input for this article.

Versions of SQL Server
This article applies to SQL Server 7.0, 2000, 2005 and 2008, where not noted otherwise.

More information
You can shrink a database file using either DBCC SHRINKDATABASE (which targets all files for the database) or DBCC SHRINKFILE (which targets a particular database file). I prefer SHRINKFILE. I will not go through the details of the commands here; they are documented in SQL Server Books Online. Let us first determine what actually happens when you shrink a database file:

Shrinking of data file
When you shrink a data file, SQL Server will first move pages towards the beginning of the file. This frees up space at the end of the file and the file can then be shrunk (or as I like to view it: "cut off at the end").

Management of transaction log file
This section is only to serve as a brief introduction to the topic of transaction log file management. See below for reference.
Each modification performed in the database is reflected in the transaction log file. Needless to say, these log records need to be removed (or actually: overwritten) sooner or later - or else we would end up with a huge log file or a full log file.
One way is to set the database to simple recovery model. This mean that SQL Server will automatically truncate the log (or, as I like to call it: "empty the log").
Another option is to have the database in full or bulk_logged recovery model and perform regular transaction log backups (BACKUP LOG). The transaction log is emptied when you backup the log. Note that the log is *not* emptied for other backup types (like BACKUP DATABASE).
See the SQL Server 2005 Books Online reference
http://msdn.microsoft.com/en-us/library/ms345583.aspx for details. Make sure you read all the subsections. It is worth the 30 minutes of your time to understand how to manage the transaction log. Trust me on this.

Shrinking of transaction log file
SQL Server cannot move log records from the end of the log file toward the beginning of the log file. This means that SQL Server can only cut down the file size if the file is empty at the end of the file. The end-most log record sets the limit of how much the transaction log can be shrunk. A transaction log file is shrunk in units of Virtual Log Files (VLF). You can see the VLF layout using the undocumented DBCC LOGINFO command, which returns one row per virtual log file for the database:

DBCC LOGINFO('myDatabase')
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 253952 8192 11 0 128 0
2 253952 262144 13 0 128 0
2 270336 516096 12 0 128 7000000025000288
2 262144 786432 14 2 128 9000000008400246

The interesting column is "Status". 0 means that the VLF is not in use and 2 means that it is in use. In my example, I have 2 at the end of the file (read result from top to bottom) and this means that the file cannot currently be shrunk.
In 7.0, you have to generate dummy transactions so that the usage of the log file wraps toward the beginning of the file. You can then empty the log file using BACKUP LOG and then shrink the file.
As of SQL Server 2000, the generation of dummy log records is done for you when you execute the DBCC SHRINKFILE command.
What you end up doing is BACKUP LOG, DBCC SHRINKFILE several times. Investigate the layout of the log file using DBCC LOGINFO in between.

If you have loads of VLF (many rows returned from DBCC LOGINFO), you probably had a small file size for the log initially and then had lots of small autogrow. Having lots of VLF is a bit of a performance hit. If this is your case, consider shrinking the log file to a very small size and then expand the file size to something comfortable (a bigger size).
Here are some articles specifically about management of log file size:
How to Shrink the SQL Server 7.0 Transaction Log
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
Log File Grows too big
Log file filling up
Considerations for Autogrow and AutoShrink

So what is the problem? Why shouldn't I shrink database files on a regular basis?
Have a look at below list and then you can determine for yourself whether or not you want to shrink database files regularly:
  • Each page moved will be logged to the transaction log.
    Say you have a database with 50GB used space (data and indexes pages), and the shrink will shuffle 40 GB towards the beginning of the file. The log file will for this shrink operation need 40GB, likely auto-grow to that size (of you don't have 40GB free space in the log file already). The following log backup wll be 40GB in size, plus the "regular" log records. This doesn't seem to happen if the database is in simple recovery mode, possibly beacuse CHECKPOINT will prune the log regurarly during the shrink.
    (Applies to shrinking of data files.)

  • After the shrink, as users add rows etc in the database, the file has to grow again.
    Growing a database file is an expensive operation, it takes time and it also hurts performance (lots of resource usage). Also, some modifications will be blocked until the grow operation has finished.
    (Applies to shrinking of both data and log files.)

    SQL Server 2005 and later:
    As of SQL Server 2005, we have "instant file initialization" which means that database files can be created and also grow very fast since Windows doesn't "zero out" the data in the database file. Instant file initialization is only available for data files, not log files. Also, instance file initialization requires that the service account for the SQL Server service has the SE_MANAGE_VOLUME_NAME windows privilige, which can be assigned using the Perform Volume Maintenance Tasks security policy. This is by default only granted to Administrators.

  • There are situations where autogrow doesn't "catch up" with the space usage requirements.
    This will result in an error message from SQL Server when the modification is performed, returned to the client application: error 1105 if data is full and 9002 if log is full.
    (Applies to shrinking of both data and log files.)

  • Moving datapages around will fragment your database.
    Say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild, leaving you with fragmented indexes. Don't believe me? This is easy to test for yourself.
    What if you do it the other way around, shrink first, then rebuild? Well, the rebuld need free space in the database for the largest index that you rebuild, and it is likely you have a large table with a clustered index. A friend of mine had a 4GB used space db, where almost all space was one 4GB table. He did a shrink and then rebuild, where the re-build immediately "bumped up" the db size to 8GB.
    (Applies to shrinking of data files.)

  • Heavy shrinking and growing of database files will fragment your file system, which will further hurt performance.
    (Applies to shrinking of both data and log files.)

Conclusion
In the end, only you can determine whether you want to shrink or not. With above information, you hopefully have enough to go on when making that decision. You probably know by now what I think of regular shrinking. Not to talk about autoshrink. :-)


Transact-SQL code
Below you find the T-SQL code I ran to prove that shrinking of a database will generate the same amount of transaction log records as the amount of data which was moved:

--Script to show that shrink produces a lot of log record.
SET NOCOUNT ON
USE
master
IF DB_ID('shrink_test') IS NOT NULL
DROP DATABASE shrink_test
GO
CREATE DATABASE shrink_test
ON PRIMARY 
(NAME = shrink_test_data,
FILENAME = N'c:\shrink_test_data.mdf',
SIZE = 5MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON 
(NAME = shrink_test_log,
FILENAME = N'c:\shrink_test_log.ldf',
SIZE = 3MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
GO

USE shrink_test
CREATE TABLE t
(c1 int identity CONSTRAINT PK_shrink_test PRIMARY KEY NONCLUSTERED, c2 char(3000) default 'hello')

DECLARE @i int
SET
@i = 1
WHILE @i <= 40000 
BEGIN
INSERT INTO
t DEFAULT VALUES
IF
@i%100 = 0 --Truncate log on every 100'th row
BACKUP LOG shrink_test WITH NO_LOG
SET @i = @i + 1
END
SELECT
COUNT(c1) AS "Number of rows before delete, should be 40000"FROM
GO

--Delete some rows, in a loop so log doesn't grow!
DECLARE @i int
SET
@i = 1
WHILE @i <= 40000 
BEGIN
IF
@i <= 20000
DELETE FROM t WHERE c1 = @i
SET @i = @i + 1
END
SELECT
COUNT(c1) AS "Number of rows after delete, shuld be 20000"FROM

--Make sure the tlog file doesn't auto-truncate
BACKUP DATABASE shrink_test TO DISK = 'NUL'

--Database file should be large, logfile small
SELECT name, size*8192/(1024*1024) AS sizeInMB FROM sysfiles
GO

--This shrink might now produce a lot of log record as 20000 rows will be moved!
CHECKPOINT
DBCC
SHRINKFILE (shrink_test_data, 40)

--Database file should now be small, but logfile large
SELECT name, size*8192/(1024*1024) AS sizeInMB FROM shrink_test..sysfiles

--My result, the data file shrunk to 80MB and the log file grew from 3MB to 146MB!!!
GO
USE master
GO
DROP DATABASE shrink_test