March 9, 2003 at 10:36 am
Hi all, Im just wondering what would DBCC ShrinkDatabase do when you run it in a full-recovery mode databases. Since the logs for full-recovery mode databases cannot be truncated until it's been backed up, I'm not sure if DBCC ShrinkDatabase will do anything at all? Please help me. Thx
March 12, 2003 at 1:24 pm
Let me take a stab at this, I'm not a SQL 2K guy yet. Full recovery mode simply means that every single transaction on the database is recorded and the log file will grow until it is backed up, not sure if you can truncate it, I would hope not. Back it up and then deleted the back up if you want.
ShrinkDatabase should care less about which mode you have chosen what it looks for is allocated space that is not being used.
For example a database was given 10 gig top start it grew to 40G and then you drop the table that caused it to grow. The database still has the 40 gig, albeit it is only using the 10gig now with 30 gig to grow. DBCC ShrinkDatabase will help you get the 30 gig back. Same applies to transaction logs but that a bit more complicated on recovering back space. Heres a script that I picked up somewhere along the line, my sincere apologies for not crediting the author,
use master
go
if object_id( 'sp__force_shrink_log' ) is not null drop proc sp__force_shrink_log
go
create proc sp__force_shrink_log
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
--
-- Parameters:
-- target_percent - Is used to stop the procedure upon reaching the
--desirable reasonable percent of remaining shrinkable space (for
--example, 20 percent). The default value of 0 instructs the
--procedure to strive for the best possible deflation.
-- target_size_MB - Will prevent the log from shrinking back beyond the
--specified amount.
-- iterations -controls the maximum number of times the loop will execute.
--The default value of 1000 should be sufficient to shrink most
-- logs completely. If it's not enough, then something might be
--wrong with the database.
-- Backup_log_opt - Determine whether the log is to be truncated or backed up.
--For databases that are log shipped this option must be left
--blank, if the db option trunc. log on checkpoint is blank then
--leave this blank, otherwise set the option to 'truncate_only'.
--Suggest replacing this line exec( 'backup log [' + @db + '] @backup_log_opt )
-- by calling existing backup job used by log shipping.
--
-------------------------------------------------------------------------------
@target_percent tinyint = 0,
@target_size_MB int = 10,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) =''
as
set nocount on
declare @db sysname,
@last_row int,
@log_size decimal(15,2),
@unused1 decimal(15,2),
@unused decimal(15,2),
@shrinkable decimal(15,2),
@iteration int,
@file_max int,
@file int,
@fileid varchar(5)
select @db = db_name(),
@iteration = 0
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateTime datetime
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @file_max = @@rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select @unused1 = @unused -- save for later
select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
while @shrinkable * 100 / @log_size > @target_percent
and @shrinkable > @target_size_MB
and @iteration < @max_iterations begin
select @iteration = @iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @file = 0
while @file < @file_max begin
select @file = @file + 1
select @fileid = fileid from #logfiles where id = @file
exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
end
EXECUTE master.dbo.xp_sqlmaint N'-PlanID A1093E8E-BA7F-11D4-8DF9-0006298F4455 -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 1WEEKS -BkExt "TRN"'
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select @last_row = @@rowcount
select @log_size = sum( FileSize ) / 1048576.00,
@unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
@shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @iteration,
'log size, MB' = @log_size,
'unused log, MB' = @unused,
'shrinkable log, MB' = @shrinkable,
'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end
if @unused1 < @unused
select 'After ' + convert( varchar, @iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB.'
union all
select'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10
else
select 'It took ' + convert( varchar, @iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @unused1 ) + ' MB to ' +
convert( varchar, @unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
go
Then there's the big discussion on how it does it and how much you get back that's not worth going into.
John Zacharkan
John Zacharkan
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply