Doing a FULL backup causes my Transaction Log to grow to 10GB!

  • LOL... Certification exam stuff is basically a total joke, in my opinion... but I have to do it...

    I just found it odd that there's a ton of stuff on HOW to do something in the cert book (70-431), but never any really good explanation of WHEN or WHY. I would find a little bit on the when and why really helpful... maybe MS doesn't think like I do...

  • pietlinden (10/11/2010)


    ...but never any really good explanation of WHEN or WHY. I would find a little bit on the when and why really helpful...

    There's a running joke that the answer to any WHEN or WHY is 'It Depends'. Would be funny if it wasn't so true. The trick is knowing (or finding) what it depends on.

    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
  • SKYBVI (10/11/2010)


    ?When our transaction log grows large and we want a quick way to shrink it, then we have to change the database recovery mode of the database we want to shrink from “full” to "simple," then truncate the log file by performing a full backup of the database, then switch back to the “full” recovery mode. By temporally changing from the Full recovery model to the Simple recovery mode, and then back, SQL Server will only keep the "active" portion of the log, which is very small. Hence, the log will be small.

    Or you could create a job to backup your transaction log every 15 minutes and not have to worry about it any more, and eliminate the risks Gail mentioned.

  • GilaMonster (10/12/2010)


    pietlinden (10/11/2010)


    ...but never any really good explanation of WHEN or WHY. I would find a little bit on the when and why really helpful...

    There's a running joke that the answer to any WHEN or WHY is 'It Depends'. Would be funny if it wasn't so true. The trick is knowing (or finding) what it depends on.

    My hat is off to you Gail, you are incredibly patient

    You go through this whole series of explanations every day with new people, even debunking the inevitable “truncate the log and shrink the database” suggestion.

    It’s like “Groundhog Day” for backups. 🙂

  • Michael Valentine Jones (10/12/2010)


    You go through this whole series of explanations every day with new people, even debunking the inevitable “truncate the log and shrink the database” suggestion.

    Yeah, I really must write an article so that I don't have to keep saying the same thing again and again.

    Oh, wait...

    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
  • GilaMonster (10/12/2010)


    Michael Valentine Jones (10/12/2010)


    You go through this whole series of explanations every day with new people, even debunking the inevitable “truncate the log and shrink the database” suggestion.

    Yeah, I really must write an article so that I don't have to keep saying the same thing again and again.

    Oh, wait...

    You should have a boilerplate answer that you just cut and paste into a single post.

    Make it in the form of a Socratic dialog so you can include all the bad suggestions and explain why they are bad practices. :discuss:

  • Michael Valentine Jones (10/12/2010)


    GilaMonster (10/12/2010)


    Michael Valentine Jones (10/12/2010)


    You go through this whole series of explanations every day with new people, even debunking the inevitable “truncate the log and shrink the database” suggestion.

    Yeah, I really must write an article so that I don't have to keep saying the same thing again and again.

    Oh, wait...

    You should have a boilerplate answer that you just cut and paste into a single post.

    Make it in the form of a Socratic dialog so you can include all the bad suggestions and explain why they are bad practices. :discuss:

    Maybe it can be programmed to auto-respond any time there's a post with keywords such as "Large" "Transaction" "log" 😉

  • Michael Valentine Jones (10/12/2010)


    GilaMonster (10/12/2010)


    Michael Valentine Jones (10/12/2010)


    You go through this whole series of explanations every day with new people, even debunking the inevitable “truncate the log and shrink the database” suggestion.

    Yeah, I really must write an article so that I don't have to keep saying the same thing again and again.

    Oh, wait...

    You should have a boilerplate answer that you just cut and paste into a single post.

    I do. It goes

    Please read through this - Managing Transaction Logs[/url]

    I do need to rewrite that article...next year.

    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
  • My hat is off to you Gail, you are incredibly patient

    You go through this whole series of explanations every day with new people, even debunking the inevitable “truncate the log and shrink the database” suggestion.

    It’s like “Groundhog Day” for backups. 🙂

    Well said Michael. I am not a DBA and have been reading this forum less than two months (for several good reasons). I can't count the number of times I've seen this scenario replayed. The unfortunate close second is in the 'Data Corruption' threads - the number of posts with a corrupt production database and no backup is stunning - I'm sure it feels like Groundhog Day for her over there too!

  • SwayneBell (10/13/2010)


    The unfortunate close second is in the 'Data Corruption' threads - the number of posts with a corrupt production database and no backup is stunning - I'm sure it feels like Groundhog Day for her over there too!

    Fortunately not.

    With the corruption questions it's almost always different forms of corruption each time. It's a challenge to identify what's damaged and how repairable it is, or isn't so it doesn't become 'oh no, not another one'.

    It's mostly the tran log questions that generate the deja-vu feeling of 'haven't I explained this three times already today?'

    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
  • GilaMonster (10/13/2010)


    SwayneBell (10/13/2010)


    The unfortunate close second is in the 'Data Corruption' threads - the number of posts with a corrupt production database and no backup is stunning - I'm sure it feels like Groundhog Day for her over there too!

    Fortunately not.

    With the corruption questions it's almost always different forms of corruption each time. It's a challenge to identify what's damaged and how repairable it is, or isn't so it doesn't become 'oh no, not another one'.

    It's mostly the tran log questions that generate the deja-vu feeling of 'haven't I explained this three times already today?'

    I'm glad to hear that. I was thinking specifically about the number of times you've asked if they had a backup and been told no. I've often thought I could hear you thumping the desk from the other side of the planet.

  • SwayneBell (10/13/2010)


    I'm glad to hear that. I was thinking specifically about the number of times you've asked if they had a backup and been told no. I've often thought I could hear you thumping the desk from the other side of the planet.

    I ask that with the full expectation that the answer will be no.

    Once the OP replied that he had a full backup from before the corruption occurred and an unbroken chain of log backups. I nearly choked on my coffee in surprise.

    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 12 posts - 16 through 26 (of 26 total)

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