Post reply

Is my log corrupted !?

  • December 28, 2006 at 5:15 am

    #176218

    Hello, everybody! Need some explanation and help here...

    I have db in simple recovery mode with large (15GB) log, which I would like to shrink. When I restore full backup to another server, log files are also restored with all the active transactions and I still cannot shrink log.

    DBCC SQLPERF(LOGSPACE)

    shows that 98% of log is used

    DBCC LOGINFO

    returns more than 400 records (virtual logs), most of them have status 2, which I assume (no thanks to BOL) mean that log is used

    DBCC OPENTRAN WITH TABLERESULTS returns

    REPL_DIST_OLD_LSN(251229:37954:1)

    REPL_NONDIST_OLD_LSN(251229:39709:1)

    Since I'm the only user connected to database, these transactions must have came from restore, although any uncommited transactions should be discarded during restore process !?

    So, in short: how can I rollback those transactions ?

    Thanks!

    Jure

  • December 28, 2006 at 6:16 am

    #680244

    was the db that you restored replicated? you may want to try sp_repldone

  • December 28, 2006 at 10:40 am

    #680316

    Becareful when you run sp_repldone on production...If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. sp_repldone should only be used for troubleshooting replication as directed by an experienced replication support professional.

    If you want to truncate the log on restored db run the following...

    backup log <> with truncate_only

    go

    dbcc shrinkfile (2, 100)

     

    MohammedU
    Microsoft SQL Server MVP

  • January 10, 2007 at 7:00 am

    #682360

    Thanks, Bivs - you were right!

    I had problems with uncommited transactions from replication. I have no idea where they came from, but obviously this database was replication publisher once upon a time

    Next problem I had with solving this was in the fact that none of related commands (sp_repldone, sp_repltrans, ...) work unless database is publisher. I was getting the same error message: "Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication."

    - so, first I created some dummy one-table replican publication

    - next i commited all replicated transactions with

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1

    - then dropped replication

    sp_dboption '', 'published', 'false'

    - and FINALLY schrinked my huge log files

    Thnx for the hint and best regards to everyone!

    Jure

  • January 10, 2007 at 12:20 pm

    #682523

    Some times you need to fool SQL server to resolve the issues...

    Thanks for the feed back

     

    MohammedU
    Microsoft SQL Server MVP

Viewing 5 posts - 1 through 4 (of 4 total)

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