Can't backup LOG - serious problem

Hello,
I can't backup log. I tray normal backup log to disk, backup with
no_log (clean log without backup). I know where is problem but don't
know how resolve it. My problem cause open transaction.
DBCC OPENTRAN:
Transaction information for database 'db_name'.
Replicated Transaction Information:
Oldest distributed LSN : (68123:350:1)
Oldest non-distributed LSN : (73169:188:1)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sys.databases (for this db):
log_reuse_wait
--
6
log_reuse_wait_desc
--
REPLICATION
I don't have setup replication, it is disabled, no publication and no
subscription. Maybe in past was.
DBCC LOGINFO return 72000 rows with status 2 .
2 253952 8192 107212 2 128 0
2 253952 262144 107213 2 128 0
2 253952 516096 107214 2 64 0
2 278528 770048 107215 2 64 0
2 253952 1048576 107216 2 128 67569000000013600332
2 253952 1302528 107217 2 128 67569000000013600332
72000 similar rows
2 253952 18823045120 144612 2 64
144608000000025500065
2 253952 18823299072 144613 2 64
144608000000025500065
2 286720 18823553024 144614 2 64
144608000000025500065
2 253952 18823839744 0 0 0 144611000000025300320
2 253952 18824093696 0 0 0 144611000000025300320
2 253952 18824347648 0 0 0 144611000000025300320
2 286720 18824601600 0 0 0 144611000000025300320
Tell me please how backup this log and shrink file
Thank you
--
Regards,
anxcompYou didn't really tell us what the problem with a TLog backup is (no error
message).
>> Tell me please how backup this log and shrink file
In an emergency, use:
Backup log dbName with truncate_only
dbcc shrinkfile('TLog fileName')
<anxcomp@.gmail.com> wrote in message
news:1191065040.370537.182100@.19g2000hsx.googlegroups.com...
> Hello,
> I can't backup log. I tray normal backup log to disk, backup with
> no_log (clean log without backup). I know where is problem but don't
> know how resolve it. My problem cause open transaction.
> DBCC OPENTRAN:
> Transaction information for database 'db_name'.
> Replicated Transaction Information:
> Oldest distributed LSN : (68123:350:1)
> Oldest non-distributed LSN : (73169:188:1)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> sys.databases (for this db):
> log_reuse_wait
> --
> 6
> log_reuse_wait_desc
> --
> REPLICATION
>
> I don't have setup replication, it is disabled, no publication and no
> subscription. Maybe in past was.
> DBCC LOGINFO return 72000 rows with status 2 .
> 2 253952 8192 107212 2 128 0
> 2 253952 262144 107213 2 128 0
> 2 253952 516096 107214 2 64 0
> 2 278528 770048 107215 2 64 0
> 2 253952 1048576 107216 2 128 67569000000013600332
> 2 253952 1302528 107217 2 128 67569000000013600332
> 72000 similar rows
> 2 253952 18823045120 144612 2 64
> 144608000000025500065
> 2 253952 18823299072 144613 2 64
> 144608000000025500065
> 2 286720 18823553024 144614 2 64
> 144608000000025500065
> 2 253952 18823839744 0 0 0 144611000000025300320
> 2 253952 18824093696 0 0 0 144611000000025300320
> 2 253952 18824347648 0 0 0 144611000000025300320
> 2 286720 18824601600 0 0 0 144611000000025300320
>
> Tell me please how backup this log and shrink file
> Thank you
> --
> Regards,
> anxcomp
>|||Chris,
The problem seems to be that SQL Server believes that the database is publisher for transactional
replication (according to output from DBCC OPENTRAN) and it counts the oldest log record that hasn't
been read by the "Log Reader" replications process as an open transaction. I.e., using TRUNCATE_ONLY
or NO_LOG won't help. The solution is to make SQL Server understand that there's no replication
going on. Possibly sp_repldone, but I'm no replication expert, so anxcomp might be best server to
ask in a replication group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ChrisR" <ChrisR@.foo.com> wrote in message news:%231b$8%23qAIHA.4836@.TK2MSFTNGP06.phx.gbl...
> You didn't really tell us what the problem with a TLog backup is (no error message).
>> Tell me please how backup this log and shrink file
> In an emergency, use:
> Backup log dbName with truncate_only
> dbcc shrinkfile('TLog fileName')
>
> <anxcomp@.gmail.com> wrote in message news:1191065040.370537.182100@.19g2000hsx.googlegroups.com...
>> Hello,
>> I can't backup log. I tray normal backup log to disk, backup with
>> no_log (clean log without backup). I know where is problem but don't
>> know how resolve it. My problem cause open transaction.
>> DBCC OPENTRAN:
>> Transaction information for database 'db_name'.
>> Replicated Transaction Information:
>> Oldest distributed LSN : (68123:350:1)
>> Oldest non-distributed LSN : (73169:188:1)
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>>
>> sys.databases (for this db):
>> log_reuse_wait
>> --
>> 6
>> log_reuse_wait_desc
>> --
>> REPLICATION
>>
>> I don't have setup replication, it is disabled, no publication and no
>> subscription. Maybe in past was.
>> DBCC LOGINFO return 72000 rows with status 2 .
>> 2 253952 8192 107212 2 128 0
>> 2 253952 262144 107213 2 128 0
>> 2 253952 516096 107214 2 64 0
>> 2 278528 770048 107215 2 64 0
>> 2 253952 1048576 107216 2 128 67569000000013600332
>> 2 253952 1302528 107217 2 128 67569000000013600332
>> 72000 similar rows
>> 2 253952 18823045120 144612 2 64
>> 144608000000025500065
>> 2 253952 18823299072 144613 2 64
>> 144608000000025500065
>> 2 286720 18823553024 144614 2 64
>> 144608000000025500065
>> 2 253952 18823839744 0 0 0 144611000000025300320
>> 2 253952 18824093696 0 0 0 144611000000025300320
>> 2 253952 18824347648 0 0 0 144611000000025300320
>> 2 286720 18824601600 0 0 0 144611000000025300320
>>
>> Tell me please how backup this log and shrink file
>> Thank you
>> --
>> Regards,
>> anxcomp
>|||TRUNCATE_ONLY or NO_LOG doesn't return any error, but it doesn't clean
no active transaction, so I can't shrink file. Log file is full
Virtual Log Files with status 2 and this mean I can't shrink file :(
All right I'll tray send this message to
microsoft.public.sqlserver.replication
Thanks
--
Regards,
anxcomp|||You might also want to Google for below. I got a number of hits, some sounded promising:
sp_repldone "log full"
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1191091375.237234.86020@.d55g2000hsg.googlegroups.com...
> TRUNCATE_ONLY or NO_LOG doesn't return any error, but it doesn't clean
> no active transaction, so I can't shrink file. Log file is full
> Virtual Log Files with status 2 and this mean I can't shrink file :(
> All right I'll tray send this message to
> microsoft.public.sqlserver.replication
> Thanks
> --
> Regards,
> anxcomp
>|||My bad, sorry. We recently had a situation where we couldn't do something
because the DB thought it was being replicated. My employee had to update
something in the sysObjects table if Im not mistaken (I'd look right now my
my wife is loading up the kids to head out).
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:43A7C6FA-C546-49F4-9B6B-6FD9EE4928E5@.microsoft.com...
> Chris,
> The problem seems to be that SQL Server believes that the database is
> publisher for transactional replication (according to output from DBCC
> OPENTRAN) and it counts the oldest log record that hasn't been read by the
> "Log Reader" replications process as an open transaction. I.e., using
> TRUNCATE_ONLY or NO_LOG won't help. The solution is to make SQL Server
> understand that there's no replication going on. Possibly sp_repldone, but
> I'm no replication expert, so anxcomp might be best server to ask in a
> replication group.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "ChrisR" <ChrisR@.foo.com> wrote in message
> news:%231b$8%23qAIHA.4836@.TK2MSFTNGP06.phx.gbl...
>> You didn't really tell us what the problem with a TLog backup is (no
>> error message).
>> Tell me please how backup this log and shrink file
>> In an emergency, use:
>> Backup log dbName with truncate_only
>> dbcc shrinkfile('TLog fileName')
>>
>> <anxcomp@.gmail.com> wrote in message
>> news:1191065040.370537.182100@.19g2000hsx.googlegroups.com...
>> Hello,
>> I can't backup log. I tray normal backup log to disk, backup with
>> no_log (clean log without backup). I know where is problem but don't
>> know how resolve it. My problem cause open transaction.
>> DBCC OPENTRAN:
>> Transaction information for database 'db_name'.
>> Replicated Transaction Information:
>> Oldest distributed LSN : (68123:350:1)
>> Oldest non-distributed LSN : (73169:188:1)
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>>
>> sys.databases (for this db):
>> log_reuse_wait
>> --
>> 6
>> log_reuse_wait_desc
>> --
>> REPLICATION
>>
>> I don't have setup replication, it is disabled, no publication and no
>> subscription. Maybe in past was.
>> DBCC LOGINFO return 72000 rows with status 2 .
>> 2 253952 8192 107212 2 128 0
>> 2 253952 262144 107213 2 128 0
>> 2 253952 516096 107214 2 64 0
>> 2 278528 770048 107215 2 64 0
>> 2 253952 1048576 107216 2 128 67569000000013600332
>> 2 253952 1302528 107217 2 128 67569000000013600332
>> 72000 similar rows
>> 2 253952 18823045120 144612 2 64
>> 144608000000025500065
>> 2 253952 18823299072 144613 2 64
>> 144608000000025500065
>> 2 286720 18823553024 144614 2 64
>> 144608000000025500065
>> 2 253952 18823839744 0 0 0 144611000000025300320
>> 2 253952 18824093696 0 0 0 144611000000025300320
>> 2 253952 18824347648 0 0 0 144611000000025300320
>> 2 286720 18824601600 0 0 0 144611000000025300320
>>
>> Tell me please how backup this log and shrink file
>> Thank you
>> --
>> Regards,
>> anxcomp
>>
>|||Hello,
Thank you Tibor. I got answer at replication group.
Bellow procedure solved my problem
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0,
@.time
= 0, @.reset = 1
Your advice with Google always was good :)
Thanks
--
Regards,
anxcomp