Forcibly nuke transaction log (SQL 2k14)

Discussion in 'Webmastering & Programming' started by lopoetve, May 16, 2019.

  1. lopoetve

    lopoetve Imhotep

    Messages:
    28,779
    Joined:
    Oct 11, 2001
    55MB database.
    Transaction log is currently 696GB. App will let that grow forever, which is undesirable, for obvious reasons - and the backups were silently broken for ~6 months, so it wasn't getting backed up either.

    DB is in an always-on group, so I've been trying to avoid putting it into simple mode, but no combination of backup w. truncate, shrink, etc seems to be releasing that space. Trying a double backup/shrink right now (every time I try I lose ~25G).

    Given the size of the DB, I'd really like to get that 600G back - and now the backups are fixed so the log backups should run in a sane fashion too. What am I missing? Is there no choice but to take the app down, take it out of the always on group, nuke it from all but one node, simple mode / backup / shrink, and then readd to the group?

    /not a DBA. Just forced to fix this.
     
  2. JVene

    JVene n00b

    Messages:
    15
    Joined:
    Apr 25, 2019
    In properties are you selecting recovery mode to full?

    Have you conducted queries to check the volume of the transaction log.

    My thinking on the last question is related to how space may be allocated. The engine may not shrink the file sizes, just make existing space in the files occupied available. If I'm correct, this means you won't see those grow for a long while. To my knowledge, you may be required to pull it offline, extract the database so that you re-create it with all the data.

    Also, not a DBA, I'm guessing - noticed this hadn't gotten any response for a while.
     
  3. lopoetve

    lopoetve Imhotep

    Messages:
    28,779
    Joined:
    Oct 11, 2001
    Yeah, currently it’s on full (required for always on groups). That’s how it handles the mirroring and shipping of data around.

    You may be right. What I need to do is dump all that data into the actual database; or truncate the damned tables (but the app publisher doesn’t list a lot of detail as to what you can truncate). They basically use the database as a log of ~everything~ that has ever happened to anything. Forever. Which is insane.

    Sadly my sql is just lacking enough that I’m not sure how to really query things.

    I’m about to the point that we take it down, switch to simple, run the backup twice, and then the log should clear. Adding back to the group shouldn’t be bad since the dB itself is pretty smallish. I’d hope. Gah. Big problem is going to be if the stupid thing grows by all 500+ G as we do that, as I don’t have the free space for that particular dance, and there’s other databases on there.
     
  4. JVene

    JVene n00b

    Messages:
    15
    Joined:
    Apr 25, 2019
    I'll dig around a bit more. I consult, and I've been where you are now in years past. What hardware is involved, and do you have any authorization to install a $60 1 TByte drive, even if it's temporary for scratch work?
     
  5. lopoetve

    lopoetve Imhotep

    Messages:
    28,779
    Joined:
    Oct 11, 2001
    It’s all virtualized and running on storage arrays; I can give it 500T if I need to, but getting the space back is harder :p. And I’d rather not give up the space forever, as our DR site is notably limited in space.
     
  6. Biznatch

    Biznatch 2[H]4U

    Messages:
    2,208
    Joined:
    Nov 16, 2009
    Are you performing the shrink on the primary? It's a write operation, so you can't run it on the secondary where backups are done.
     
  7. lopoetve

    lopoetve Imhotep

    Messages:
    28,779
    Joined:
    Oct 11, 2001
    Yep. Connected to the master.
     
  8. cyklondx

    cyklondx [H]Lite

    Messages:
    76
    Joined:
    Mar 19, 2018
    Try making transact backup,

    if that doesn't work you will need to set it to simple; then shrink the log - set it to full and make another full backup. Make sure transnational run every X minutes, every 6h a diff, and every 24h a full backups prefered so you do not have to deal with this in future.