Transaction File Not budging

  • I have a test database (ID 2) that is 13 G and has a transaction log of 5 G.

    It was in Simple recovery mode.

    It now is in Full recovery mode in order to try to shrink the log.

    The live database us 14G and less than 1G transaction log.

    It is in simple mode.

    Both databases receive data from replication servers.

    I have tried manual log backups and maintenance plans and:

    DBCC SHRINKFILE(2, 1)

    BACKUP LOG [test] WITH TRUNCATE_ONLY

    DBCC SHRINKFILE(2, 1)

    Nothing appears to shrink it.

    Any suggestions out there?

    Thanks

    Graham

    Graham Okely B App Sc
    Senior Database Administrator

  • Assuming 2005, query the sys.database view and check the value of the column log_reuse_wait_desc (or something similar). That tells you the reason that the log space cannot be reused.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQL 2000

    😀

    Graham Okely B App Sc
    Senior Database Administrator

  • Then rather post in the SQL 2000 forum please. Posting in the 2005 forum, you will get just that, a useless suggestion that wasted both out times.

    Now... Is the database in question published? Was it ever published?

    What does DBCC OPENTRAN return?

    What does DBCC SQLPERF(logspace) return?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • opps sorry, I did not notice that it was 2005 forum, forgive me.

    The test db subscribes to another database.

    I do not know if it was ever published, it is not published now.

    DBCC SQLPERF(logspace) give 95% of logspace used for db in question.

    DBCC OPENTRAN

    Gives

    Replicated Transaction Information:

    Oldest distributed LSN : (32471:27046:1)

    Oldest non-distributed LSN : (32471:29355:1)

    Thank you

    Graham

    Graham Okely B App Sc
    Senior Database Administrator

  • Firstly, you can set the DB back to simple recovery mode. You can shrink the logs insimple, you just can't back them up.

    The open tran info shows that the DB thinks it's published.

    (Oldest distributed LSN : (32471:27046:1)

    Oldest non-distributed LSN : (32471:29355:1))

    That refers to the log entries that have not been passed on to the distributor. Was it restored from a backup of a published DB? Sometimes the published status partially stays with a restored database. Enough to stuff the logs up anyway.

    Go to query analyser and run the following (only if you are very sure this DB is not supposed to be published!!!!!!!)

    exec sp_repldone NULL -- mark all log entries as distributed

    checkpoint -- auto-truncate the log if in simple recovery

    Now run DBCC SQLPERF (logspace) and see if the usage has dropped.

    The permainent fix is to set up a publication on that DB (it doesn't matter of what) then drop the publication. That should clear out the reminents of replication. A check of DBCC OPENTRAN after that should have no references to replicated LSNs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail

    Many thanks, that advice has resolved the difficulty.

    It turns out the database was a restore form a published database.

    Plan

    ?Backed up the test db

    ?Placed it into Simple recovery model

    ?Published a small random table

    ?Deleted that publication

    ?Ran DBCC SHRINKFILE(FILE_ID, 1)

    ?Ran BACKUP LOG [test db] WITH TRUNCATE_ONLY

    ?Ran DBCC SHRINKFILE(FILE_ID, 1)

    ?DBCC OPENTRAN gave clear results

    The log came from 5GB to 50MB!

    Regards

    Graham Okely B App Sc
    Senior Database Administrator

Viewing 7 posts - 1 through 7 (of 7 total)

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