BI cooking

with a touch of SQL
posts - 5 , comments - 0 , trackbacks - 0

Force shrink of transaction log after a full backup

After installing vCenter and connecting it to a SQL Server 2008R2 database server and making it high available with the help of database mirroring, somthing anoying started to take place. After a full backup the transaction log does NOT get 'truncated' and shrunk. So within a day or 5 my transaction log drive was full and vCenter refused to operate. After a bit of research it turns out that vCenter keeps an open transaction to the tailend of the transaction. Even during a full backup. So this open transaction stops any truncation and or shrinking of the transaction log. The VMWare white paper states that a succesfull backup of the database will only happen if you stop the vCenter service, make a backup and then start the vCenter service again. Due to several reasons, this is not a preffered way of ensuring a proper backup with a shrunk transaction log. The following code gives you the option to force a shrink of the transaction log.

Only use this solution if the possible dataloss is acceptable.

---------------------------------------------------------------------
-- Backup_Tran
---------------------------------------------------------------------
-- Author: Remko de Boer
-- Created: 2012-06-18
-- Description: Create a transaction log backup for all databases that are 
-- mirrored and in a synchronised state
---------------------------------------------------------------------
-- Version: 1.0 initial
---------------------------------------------------------------------
-- Database state:
-- 0 = ONLINE 
-- 1 = RESTORING
-- 2 = RECOVERING
-- 3 = RECOVERY_PENDING
-- 4 = SUSPECT
-- 5 = EMERGENCY
-- 6 = OFFLINE
--
-- Mirroring state:
-- 0 = Suspended
-- 1 = Disconnected from the other partner
-- 2 = Synchronizing 
-- 3 = Pending Failover
-- 4 = Synchronized
-- 5 = The partners are not synchronized. Failover is not possible now.
-- 6 = The partners are synchronized. Failover is potentially possible.
--
-- Shrink enabled
-- 0 = No
-- 1 = Yes
--
-- Overwrite backup
-- 0 = No, backup files will be appended
-- 1 = Yes, previous backups will be overwritten
---------------------------------------------------------------------
ALTER proc [dbo].[spBackup_tran]
AS
-- Declare Variables ------------------------------------------------
declare @name varchar(150)
declare @id int
declare @backup_root varchar(500)
declare @backup_path varchar(250)
declare @backup_file varchar(250)
declare @mirror_state int
declare @database_state int
declare @shrink_enabled int
declare @overwrite_backup int
declare @backup_date varchar(20)
declare @kill int
declare @killstring nvarchar(1000)
---------------------------------------------------------------------
 
-- Set Variables ----------------------------------------------------
set @backup_root = 'D:\Beheer\SQL_Backup\'
set @mirror_state = 4
set @database_state = 0
set @shrink_enabled = 1
set @overwrite_backup = 1
set @kill = 0
---------------------------------------------------------------------
 
---------------------------------------------------------------------
set @backup_path = @backup_root + CAST(@@SERVERNAME as varchar) + '\'
set @backup_date = replace(replace(replace(convert(varchar,CONVERT(datetime,getdate(),120),120),'-',''),' ',''),':','')
--select @backup_date
---------------------------------------------------------------------
 
declare transactie cursor for
select
db.name,
db.database_id
from 
sys.databases db
inner join sys.database_mirroring dbm on db.database_id = dbm.database_id
where 
db.[state] = @database_state
and db.recovery_model = 1
and dbm.mirroring_state = @mirror_state
open transactie
FETCH NEXT FROM transactie
INTO @name, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- set database to single user -------------------------------------------
if @kill = 1
BEGIN
set @killstring = N'ALTER DATABASE [' + @name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 
exec sp_executesql @killstring
END
--------------------------------------------------------------------------
 
 
-- Backup transactielog --------------------------------------------------
set @backup_file = ''
 
-- DEBUG -------------
-- select @backup_file
----------------------
 
 
if @overwrite_backup = 0
BEGIN
set @backup_file = @backup_path + @name + '_' + @backup_date + '.trn'
 
BACKUP LOG @name TO DISK = @backup_file WITH NOINIT
END
 
if @overwrite_backup = 1
BEGIN
set @backup_file = @backup_path + @name + '.trn'
 
BACKUP LOG @name TO DISK = @backup_file WITH INIT
END
--------------------------------------------------------------------------
 
 
-- shrink transactielog --------------------------------------------------
if @shrink_enabled = 1
BEGIN
declare @string nvarchar(500)
declare @log_name varchar(250)
DECLARE @ParmDefinition nvarchar(500)
declare @string1 nvarchar(500)
set @string = N'select @log= name from ' + @name + '.sys.database_files where type = 1'
SET @ParmDefinition = N'@log varchar(250) OUTPUT';
execute sp_executesql @string, @ParmDefinition, @log = @log_name output
set @string1 = 'use ' + @name + ' DBCC SHRINKFILE ([' + @log_name + '],1, TRUNCATEONLY)'
-- DEBUG ----------
--select @log_name
--select @string1
-------------------
 
execute sp_executesql @string1
 
 
END
--------------------------------------------------------------------------
 
-- set database to multi user -------------------------------------------
if @kill = 1
BEGIN
set @killstring = 'ALTER DATABASE [' + @name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE'
 
execute sp_Executesql @killstring 
END
--------------------------------------------------------------------------
 
 
FETCH NEXT FROM transactie
INTO @name, @id
END
CLOSE transactie
DEALLOCATE transactie

Print | posted on Wednesday, January 16, 2013 3:07 PM |

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: