Can't Shrink the Transaction Log

Question

Post reply

Can't Shrink the Transaction Log

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

These are the LSN's that I get when I perform the following:

CREATE TABLE #OpenTranStatus (

ActiveTransaction varchar(25),

Details sql_variant

)

-- Execute the command, putting the results in the table.

INSERT INTO #OpenTranStatus

EXEC ('DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS');

-- Display the results.

SELECT * FROM #OpenTranStatus;

GO

ActiveTransaction Details

REPL_DIST_OLD_LSN(0:0:0)

REPL_NONDIST_OLD_LSN(155357:249:1)

I checked in Replication Monitor and all of the Replication is failing.

One is failing and states that the script failed for a table. I go to the subscriber and there are no tables.

I check another and there is an error that it could not Bulk Copy into a Table. I looked at the table and it looks fine.

Another has Session of the Distribution Agent as Completed but the Action message is: The initial snapshot for publication "MyDatabase' is not yet available.

Another error is that it could not read the file in the Folder where the replication files are located.

There are a number of other errors but this is beyond the scope of my original post.

From what I understand someone created Replication without authorization and it appears someone went in and attempted to take actions that would make it stop.

I appreciate all of the input.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 2

Seems to me you should drop that replication.

If trying to do that leads to errors, you might do the sp_repldone as a quick fix.

Be advised that you will have to repeat that command regularly before any log backups - the new transactions will not have been replicated again and the log will not be cut...

That way you will have more time to properly end or recreate the replication...

Best regards
karl

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Karl,

I agree that replication should be dropped but I can't do that without an ok from someone.

As far as the sp_repldone it looks like my only option at this time.

In the following code there is A USE [Distribution], how do I indentify the distribution database?:unsure:

use [database]

go

exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1With success being confirmed via the following check:

use [distribution]

go

exec sp_browsereplcmds

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Created a new thread in the Replication forum since the topic has changed.

http://www.sqlservercentral.com/Forums/Topic1213268-291-1.aspx?Update=1

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 5

Hi Craig,

the sp_repldone should be done inside the problem database. [distribution] is the database used by the replication process.

Best regards
karl

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Karl Klingler (11/29/2011)
Hi Craig,
the sp_repldone should be done inside the problem database. [distribution] is the database used by the replication process.

I executed it in the Publication Database and I get the following error:

Msg 21482, Level 16, State 1, Procedure sp_browsereplcmds, Line 20

sp_browsereplcmds can only be executed in the "distribution" database.

I'm doing something wrong.:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 7

Perhaps there is/was an external distribution server configured?

Anyway - is it now possible to backup and then shrink the logfile?

Best regards
karl