Post reply

Reducing transaction log disk space

  • February 2, 2016 at 8:16 am

    #307681

    Hey guys,

    I have many databases pointing their transaction logs to a same location (G: drive in my case). I would like to reduce the size of G: thru vmware because it looks like I have allocated to much space. My plan is to create a smaller drive, call it G: again, copy over the files and point all the logs to the new G:. Would I need to detach/attach all databases for that? Also, there is a replication process on one of the databases, would this process break anything?

    Thanks.

  • February 2, 2016 at 8:22 am

    #1855999

    Create your new drive - call it H or something. Stop replication. Take the databases offline. Copy everything from G to H. Take G offline and rename H to G. Bring the databases back online. Restart replication. I think that covers it all - if I've missed something out I'm sure others will flag it up.

    John

  • February 2, 2016 at 8:35 am

    #1856005

    dembis (2/2/2016)

    Hey guys,

    I have many databases pointing their transaction logs to a same location (G: drive in my case). I would like to reduce the size of G: thru vmware because it looks like I have allocated to much space. My plan is to create a smaller drive, call it G: again, copy over the files and point all the logs to the new G:. Would I need to detach/attach all databases for that? Also, there is a replication process on one of the databases, would this process break anything?

    Thanks.

    What is the total size of your log files and what is the size of the current G: drive? Also, what is the total size of all your transaction log backup files for a 24 hour period and how many days do you want your system to be able to survive if the connection to the target of replication fails?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 2, 2016 at 8:57 am

    #1856014

    Thank you John

  • February 2, 2016 at 9:12 am

    #1856018

    I do not really care about the transaction logs. In fact the main database is on simple mode. For the replication, preferably, I would not like to lose anything.

  • February 2, 2016 at 1:36 pm

    #1856110

    dembis (2/2/2016)

    I do not really care about the transaction logs. In fact the main database is on simple mode. For the replication, preferably, I would not like to lose anything.

    Yes, you do. SQL Server will not work without transaction logs.

    Transaction logs are always needed, even in simple recovery mode. They are used to rollback failed transactions, and to ensure that your database comes back online in a consistent state if your server ever unexpectedly goes down.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • February 2, 2016 at 3:04 pm

    #1856130

    Correct. I meant that we are not counting on the transaction logs to rollback or roll-forward the data. We only rely on the latest backup in case something happen to the server. Thank very much for your time.

  • February 2, 2016 at 3:32 pm

    #1856138

    dembis (2/2/2016)

    I do not really care about the transaction logs. In fact the main database is on simple mode. For the replication, preferably, I would not like to lose anything.

    Actually, you do. If the target of your replication is interrupted, your log files cannot be truncated while replication is "active". If the drive for your log files is too small, your system might come to a screeching halt in a matter of minutes if such a thing were to occur.

    The problem is that you're in the SIMPLE Recovery Model and so have little clue as to how much space to plan for because you don't actually know how much data is logged on a daily basis. It also sounds like you don't know what caused the large growth to begin with (probably an interruption like I was describing).

    Without the answers to those question, I recommend not doing anything to the log files or where they're located at.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 4, 2016 at 5:07 am

    #1856491

    dembis (2/2/2016)

    Hey guys,

    I have many databases pointing their transaction logs to a same location (G: drive in my case). I would like to reduce the size of G: thru vmware because it looks like I have allocated to much space. My plan is to create a smaller drive, call it G: again, copy over the files and point all the logs to the new G:. Would I need to detach/attach all databases for that? Also, there is a replication process on one of the databases, would this process break anything?

    Thanks.

    How much space are we talking here, what size currently, what size are you reducing too.

    My best advice to you would be to leave this alone as you have shown a clear misunderstanding of the requirements behind the logging architecture in SQL Server, you may be setting yourself up for a fall

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 9 posts - 1 through 8 (of 8 total)

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