Post reply

Transaction Log doesn't grow!!!!

  • June 5, 2012 at 6:55 am

    #261962

    After 15 years of SQL server development, I thought I knew what I was doing...

    I'm trying to create a scenario in development to create a large transaction log to replicate an installation on client site that I inherited.

    So I'm blatting my database with inserts/deletes and updates and sure enough the transaction log starts getting filled up - so far so good gets to about 50% full, but leave it a couple of minutes and dbcc sqlperf(logspace) reports that the Log Space used is a percent or two for a 1GB log file - i.e. if it is to be believed that transaction log has been cleared out.

    It's SQL Server 2008 R2 with Full database recovery model.

    I have no backups going (data or log).

    Any ideas or am I just having a senior moment???

    Thanks,

    J

  • June 5, 2012 at 7:04 am

    #1497026

    johntaylor (6/5/2012)

    I have no backups going (data or log).

    There's the reason.

    Until there is a full backup to start the log chain, the database behaves as though it were in simple recovery

    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

  • June 5, 2012 at 7:09 am

    #1497028

    Doh ! Good call.

    I'll give it a try.

  • June 5, 2012 at 7:26 am

    #1497038

    Good One 🙂

    Cheer Satish 🙂

  • June 5, 2012 at 8:50 am

    #1497097

    And indeed, that did the business, as did the subsequent backup and toggling between Full and Simple Recovery,

    Thanks.

  • June 5, 2012 at 9:07 am

    #1497108

    Any time you switch from simple recovery (or create a new DB), you will need a full or diff backup to start the log chain before SQ: will start retaining log records.

    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

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

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