August 15, 2002 at 9:48 am
I have database with multiple filegroups along with transaction log on diffrent filegroup. To be precise Database A has file groups P1(Primary) S1(Secondory) I1(Index) and L1(Transaction Log). But due to transaction log full problem we increased the database size to real high and now we need to reduce database filegroup size. Does anybody have secret tricks to do this? Books online says database size cannot be reduced.
FYI. I don't mean DB Shrink. I need more space on hard drive.
Edited by - ext5gxp on 08/15/2002 2:10:38 PM
August 15, 2002 at 7:41 pm
Take a look at DBCC SHRINKFILE, which does allow you to shrink the file itself.
K. Brian Kelley
http://www.truthsolutions.com/
K. Brian Kelley
@kbriankelley
August 16, 2002 at 8:47 am
Thanks. It works.
But for some transaction logs it gives me following error. "Cannot shrink log file 2 (L490SU001) because total number of logical log files cannot be fewer than 2." And it does not decrease the size too.
Edited by - ext5gxp on 08/16/2002 08:50:11 AM
August 16, 2002 at 9:24 am
Try this:
create proc sp__shrinklog
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database.
-- Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999
--------------------------------------------------------------------------------
@target_percent tinyint = 0,
@max_iterations int = 1000,
@backup_log_opt nvarchar(1000) = 'with truncate_only'
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
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 varchar(50)
)
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 @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
backup log ' + @db + ' ' + @backup_log_opt
)
delete #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
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' )
Be great!
Be great!
Michael
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply