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

 

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

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

목적 : 트랜잭션 백업

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

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

• 백업 속도 향상
  
• http://www.brentozar.com/archive/2014/01/improving-the-performance-of-backups/
• http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

SQL2000

SQL2005

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  

   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

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