Results 1 to 6 of 6

Thread: Transaction log

  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Transaction log

    Hi folks,

    As a novice on SQL Server, I did a default installation of Express 2008. After a while, C: drive got full because the transaction log had unrestricted growth.

    First thing I did was to find out how to get some free space back. I discovered shrinking the log file, and it dropped from 15GB to 40MB.

    Questions:
    a) the Shrink file dialog showed 99% free; why did the file keep growing if it was vastly 'free'?
    b) why does it not recycle the free space instead?
    c) what happens to the DB when such an event (no room to grow log file) occurs?

    Next, I observed the recovery model was 'Simple'.

    Question:
    d) how does this relate to the transaction log, in terms of purpose, content, growth, maintenance, etc.

    Then, I amended the setting to restrict growth to 10GB.

    Questions:
    e) is this figure an overkill?
    f) how do I figure out an optimum value?
    g) what will happen when (if?) it reaches 10GB? eg. recycle?
    h) how do I inspect what is currently in the file?


    Many thanx in advance,
    Shah

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, you have a lot of reading ahead of you. I only skimmed it, but this will give you some of the basics:
    Managing Transaction Logs in SQL Server

  3. #3
    Join Date
    Sep 2011
    Posts
    31
    a) the Shrink file dialog showed 99% free; why did the file keep growing if it was vastly 'free'?
    - Do you have maintenance plans scheduled? like reindexing, dbcc checkdb? They use a lot of log space.
    b) why does it not recycle the free space instead?
    - You already said the file has 99% free space which means sql server is going to reuse the space
    c) what happens to the DB when such an event (no room to grow log file) occurs?
    - Generally this will happen when log file growth is restricted OR it is unrestriccted and drive is full. In such case, you wont be able to perform DML operations (any operation that utilizes log file)
    Next, I observed the recovery model was 'Simple'.
    - Log file can grow if you have transactional replication setup and log reader agent is not running even in simple recovery model

  4. #4
    Join Date
    Oct 2011
    Posts
    3
    Quote Originally Posted by MCrowley View Post
    Well, you have a lot of reading ahead of you. I only skimmed it, but this will give you some of the basics:
    Managing Transaction Logs in SQL Server
    Thanx MCrowley, I had actually read that article before, that was how I learnt some things like shrinking the file. It is a pretty good article and maybe it is a failing of me, but it did not address my questions (and even resulted in my further questions)

    For example, he wrote, in reference to the Simple model:
    The model also automatically reclaims log space, so there is almost no need to manage the transaction log space.

    So why did it have to grow till so big if it is automatic and I found it "vastly free"?

  5. #5
    Join Date
    Oct 2011
    Posts
    3
    Quote Originally Posted by cindyaz View Post
    a) the Shrink file dialog showed 99% free; why did the file keep growing if it was vastly 'free'?
    - Do you have maintenance plans scheduled? like reindexing, dbcc checkdb? They use a lot of log space.
    b) why does it not recycle the free space instead?
    - You already said the file has 99% free space which means sql server is going to reuse the space
    c) what happens to the DB when such an event (no room to grow log file) occurs?
    - Generally this will happen when log file growth is restricted OR it is unrestriccted and drive is full. In such case, you wont be able to perform DML operations (any operation that utilizes log file)
    Next, I observed the recovery model was 'Simple'.
    - Log file can grow if you have transactional replication setup and log reader agent is not running even in simple recovery model
    Hi cindyaz,

    Thanx for your response. As mentioned, this is Express, so there is no maintenance plan.

    I accept that it is _going_ to use the free space, but why did it grow so much in the first place? From an initial 130MB to 15GB without reusing?

    DML is data manipulation language? Like insert and update?
    So what will happen? SQL Server will reject those operations; will it log to Event Viewer?

    Not sure about "transactional replication setup and log reader agent" until I next get access to the server. But this is default Express installation/setup, so I doubt this applies.

    Pardon my numerous questions


    Thanx & Rgds,
    Shah

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For a database in Simple recovery mode, the log will grow to the size of the largest transaction. This happens to that the transaction can be rolled back entirely, if need be (due to an error, for example). If you watched the log usage during this transaction you would see a large amount of it used. After a few minutes of that transaction being committed, then the log is freed up. At a guess, I would think you have some large data load, perhaps deleting an entire table, then re-loading it from some other source. This is a usual culprit in a large transaction log in a database that is in simple recovery mode.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •