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

Cannot shrink database log

I backup database and then truncate log with no_log option, then try to shrink the log file. It doesnot get shrunk
Also, Checked the sysprocesses. there are writelog under lastwaittype
Please help
Thanks
SanWhat about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||Hi Dinesh
I have tried
1. backup database db to disk = 'c:\db.bak
2. backup log db with truncate_onl
3. dbcc shrinkfile(db_log,10
4. dbcc shrinkdatabase(db,20
Nothing works...!|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Lo
Thanks once again for all your valuable help
San|||Thanks a lot Jim... I will try in future when it happens again..for now I got my solution.
Thank you Jim.

Cannot shrink database log

I backup database and then truncate log with no_log option, then try to shrink the log file. It doesnot get shrunk.
Also, Checked the sysprocesses. there are writelog under lastwaittype.
Please help.
Thanks,
San
What about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San
|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San
|||Hi Dinesh,
I have tried:
1. backup database db to disk = 'c:\db.bak'
2. backup log db with truncate_only
3. dbcc shrinkfile(db_log,10)
4. dbcc shrinkdatabase(db,20)
Nothing works...!
|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..!
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Log
Thanks once again for all your valuable help.
San
|||Thanks a lot Jim... I will try in future when it happens again..for now I got my solution..
Thank you Jim.

Cannot shrink database log

I backup database and then truncate log with no_log option, then try to shr
ink the log file. It doesnot get shrunk.
Also, Checked the sysprocesses. there are writelog under lastwaittype.
Please help.
Thanks,
SanWhat about BACKUP LOG WITH NO_LOG? This should whack the t-log w/o having
to shrink. If this doesn't work, can you stop and restart the SQL service
and try the same thing again?
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||San,
For SQL7
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/?ID=256650 and/or
www.sqlserverfaq.com and search for "sql7shrinklognowork.txt"
For SQL2000
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/?ID=272318
In case the log doesnt shrink even after DBCC SHRINKFILE, then do a
combination of...
Back up the Database (BACKUP DATABASE dbname...)
Back up the log (BACKUP LOG dbname...)
Truncate the log (BACKUP LOG dbname WITH TRUNCATE_ONLY)
Shrink the file (DBCC SHRINKFILE...)
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:57CF303C-1C95-4094-8D3A-E404E8C3E8D7@.microsoft.com...
> I backup database and then truncate log with no_log option, then try to
shrink the log file. It doesnot get shrunk.
> Also, Checked the sysprocesses. there are writelog under lastwaittype.
> Please help.
> Thanks,
> San|||Hi Dinesh,
I have tried:
1. backup database db to disk = 'c:\db.bak'
2. backup log db with truncate_only
3. dbcc shrinkfile(db_log,10)
4. dbcc shrinkdatabase(db,20)
Nothing works...!|||Thanks Dinesh...This indeed worked
The script presented to wrap aroun the tx log help solved it..!
Microsoft Knowledge Base Article - 256650
INF: How to Shrink the SQL Server 7.0 Transaction Log
Thanks once again for all your valuable help.
San|||Thanks a lot Jim... I will try in future when it happens again..for now I go
t my solution..
Thank you Jim.