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
>

Problem with Drop Table and Field name chage

Hi!
I had two servers of SQL 2000 with distributor and publisher mode for replications. I wanted to change one field name of a table, I did the following:
1. stopped publishing,
2. Stopped distribution
3.Re-started the servers.

But when I want to save the changed field name i am getting this error

"/*

Saturday, October 18, 2003 12:59:08 PM

User: sa

Server: SSD-SQL2

Database: PAIFORWEB

Application: MS SQLEM - Data Tools

*/

'CC02' table
- Unable to rename column from 'INWARD' to 'INWARDxyz'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot rename the table because it is published for replication.
"

I am getting the same error while dropping the database toout when I checked the replication there is no distribution and publication is existing.

Please help, it is most urgent to me.

Thanks in advance.

Sabyasachi Sensearch for "manually removing replication" in google prior to proceeding with object rename.