Problem with large transaction log

I have a database i revocery mode simple (a test-system) where the
transactionlog have grown to much (6 GB and the db size are 300 MB) and I
have problem to shrink it. When I try to shrink it it says "Cannot shrink
log file 2 (DBNAME_log) because all logical log files are in use. What I can
see I'm the only one using it (SQL Srv Man Studio)
If I run DBCC SQLPERF(Logspace) it says 99%
If I run backup log DBNAME with truncate_only it's success but nothing
disappears.
I also have done a full backup ...
What can I do ? It feels like the checkpoint never are run but it must have.
I have tried alter it between recovery mode full and backup to simple
Please help me out
Sincerely
\\Jonas Bjonte@.bson.se wrote:
> I have a database i revocery mode simple (a test-system) where the
> transactionlog have grown to much (6 GB and the db size are 300 MB) and I
> have problem to shrink it. When I try to shrink it it says "Cannot shrink
> log file 2 (DBNAME_log) because all logical log files are in use. What I can
> see I'm the only one using it (SQL Srv Man Studio)
> If I run DBCC SQLPERF(Logspace) it says 99%
> If I run backup log DBNAME with truncate_only it's success but nothing
> disappears.
> I also have done a full backup ...
> What can I do ? It feels like the checkpoint never are run but it must have.
> I have tried alter it between recovery mode full and backup to simple
> Please help me out
> Sincerely
> \\Jonas B
>
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sp_force_shrink_log]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_force_shrink_log]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_force_shrink_log
/*
*************************************************************
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>,
<iterations>, <backup options>
exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@.azdatabases.com
Input Params:
--
@.target_percent tinyint. default = 0. Target percentage of remaining
shrinkable
space. Defaults to max possible.
@.target_size_MB int. default = 10. Target size of final log in MB.
@.max_iterations int. default = 1000. Number of loops (max) to run proc
through.
@.backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup
options.
Output Params:
--
Return:
Results:
--
Locals:
--
@.err Holds error value
Modifications:
--
*************************************************************
*/
@.target_percent tinyint = 0,
@.target_size_MB int = 10,
@.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,
@.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,
CreateLSN real
)
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, CreateLSN) 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
exec( 'backup log [' + @.db + '] ' + @.backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status,
Parity, CreateLSN) 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Hi
If you have FULL recovery model , BACKUP LOG file and then run DBCC
SHRINKFILE command
It seeems you have active transactions with status =2 ( could be seen byb
running DBCC LOGINFO(dbid))
Run demo INSERT to move the transactions (with status =2) on the top of the
list and then run DBCC shrinkfile
"jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:%23Sp$doaYIHA.6044@.TK2MSFTNGP05.phx.gbl...
>I have a database i revocery mode simple (a test-system) where the
>transactionlog have grown to much (6 GB and the db size are 300 MB) and I
>have problem to shrink it. When I try to shrink it it says "Cannot shrink
>log file 2 (DBNAME_log) because all logical log files are in use. What I
>can see I'm the only one using it (SQL Srv Man Studio)
> If I run DBCC SQLPERF(Logspace) it says 99%
> If I run backup log DBNAME with truncate_only it's success but nothing
> disappears.
> I also have done a full backup ...
> What can I do ? It feels like the checkpoint never are run but it must
> have. I have tried alter it between recovery mode full and backup to
> simple
> Please help me out
> Sincerely
> \\Jonas B
>|||My guess is that you have some open or un-replicated transactions in the database. Use DBCC OPENTRAN
(see Books Online).
As for shrinking (after you have resolved your open transaction), check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp which has some general info about shrinking
log files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:%23Sp$doaYIHA.6044@.TK2MSFTNGP05.phx.gbl...
>I have a database i revocery mode simple (a test-system) where the transactionlog have grown to
>much (6 GB and the db size are 300 MB) and I have problem to shrink it. When I try to shrink it it
>says "Cannot shrink log file 2 (DBNAME_log) because all logical log files are in use. What I can
>see I'm the only one using it (SQL Srv Man Studio)
> If I run DBCC SQLPERF(Logspace) it says 99%
> If I run backup log DBNAME with truncate_only it's success but nothing disappears.
> I also have done a full backup ...
> What can I do ? It feels like the checkpoint never are run but it must have. I have tried alter it
> between recovery mode full and backup to simple
> Please help me out
> Sincerely
> \\Jonas B
>|||Yes you are right, the database are replicated, that I didn't know so I have
open transactions. Now I think I can solve it.
Thank's for your help
Sincerely
\\Jonas B
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
meddelandet news:e7jiKlbYIHA.4448@.TK2MSFTNGP03.phx.gbl...
> My guess is that you have some open or un-replicated transactions in the
> database. Use DBCC OPENTRAN (see Books Online).
> As for shrinking (after you have resolved your open transaction), check
> out http://www.karaszi.com/SQLServer/info_dont_shrink.asp which has some
> general info about shrinking log files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
> news:%23Sp$doaYIHA.6044@.TK2MSFTNGP05.phx.gbl...
>>I have a database i revocery mode simple (a test-system) where the
>>transactionlog have grown to much (6 GB and the db size are 300 MB) and I
>>have problem to shrink it. When I try to shrink it it says "Cannot shrink
>>log file 2 (DBNAME_log) because all logical log files are in use. What I
>>can see I'm the only one using it (SQL Srv Man Studio)
>> If I run DBCC SQLPERF(Logspace) it says 99%
>> If I run backup log DBNAME with truncate_only it's success but nothing
>> disappears.
>> I also have done a full backup ...
>> What can I do ? It feels like the checkpoint never are run but it must
>> have. I have tried alter it between recovery mode full and backup to
>> simple
>> Please help me out
>> Sincerely
>> \\Jonas B
>