Shrink log file

  • I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)

    The database had been set to Full recovery mode but there were no regularly scheduled TLOG backups running.

    I'd like to reclaim some of the space back to the OS from the log file. I ran a couple of TLOG backups and a full backup. Now I'd like to shrink the logs. How would I go about doing that?

  • shrinking the log - Bad option.

    file will grow again to the size it required.

    Is it that space crunch?

    Regards
    Durai Nagarajan

  • yes, there is a space crunch. The other reason I wanted to shrink was because there were no Tlog backups running, there is a ton of free space in the log. when I run DBCC SQLPERF(logspace) - it is showing that I am only actually using 5% of the 17GB in these log files.

    I wanted to shrink and then schedule regular log backups.

  • Truncate log will break the backup chain.

    in order to create a new chain you have to start with a full backup and then proceeded by log backups.

    do remember once the file grown to some size it wont release the space automatically to OS.

    for shrinking the log

    USE DatabaseName

    GO

    DBCC SHRINKFILE(<TransactionLogName>, 1)

    BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

    DBCC SHRINKFILE(<TransactionLogName>, 1)

    post this take a full backup and then schedule log backup.

    try to avoid if you can get more space.

    Regards
    Durai Nagarajan

  • would this also work?

    Use [my database]

    alter database [my database] set recovery simple

    --Backup LOG with NO_LOG

    Use [my database]

    BACKUP LOG [my database] WITH NO_LOG

    --Shrink log files

    Use [my database]

    DBCC SHRINKFILE (1)

    DBCC SHRINKFILE (2)

    --Change recovery mode back to FULL

    Use [my database]

    alter database [my database] set recovery FULL

    -- FULL database backup when completed

  • yes but DBCC SHRINKFILE (1) will shrink the data file - avoid this

    Regards
    Durai Nagarajan

  • Jpotucek (4/17/2013)


    I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)

    The database had been set to Full recovery mode but there were no regularly scheduled TLOG backups running.

    I'd like to reclaim some of the space back to the OS from the log file. I ran a couple of TLOG backups and a full backup. Now I'd like to shrink the logs. How would I go about doing that?

    You are not taking a t-log backup then is there any reason to place a DB in full recovery.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • It's OK to shrink files if the reason they've grown isn't likely to be repeated. This is indeed the case in your situation - somebody set the database to full recovery but didn't schedule any log backups.

    There's no need to change the recovery mode to shrink the logs. Just take a full backup, shrink the logs to the maximum size you think they'ill need, take another full backup, and check that you have transaction log backups scheduled at a suitable frequency.

    John

  • exactly. I would like to reclaim some of the space and then schedule regular Tlog backups. That is the plan. thank you!!

  • Jpotucek (4/17/2013)


    exactly. I would like to reclaim some of the space and then schedule regular Tlog backups. That is the plan. thank you!!

    Glad to hear this 😀

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • John Mitchell-245523 (4/17/2013)


    It's OK to shrink files if the reason they've grown isn't likely to be repeated. This is indeed the case in your situation - somebody set the database to full recovery but didn't schedule any log backups.

    There's no need to change the recovery mode to shrink the logs. Just take a full backup, shrink the logs to the maximum size you think they'ill need, take another full backup, and check that you have transaction log backups scheduled at a suitable frequency.

    John

    A full backup will not do anything with the log files. To mark the space usable in the transaction log files - you have to perform a transaction log backup when the database is in full or bulk-logged recovery model.

    To get this space back, perform a transaction log backup and then run: DBCC LOGINFO in that database. Look for a 2 in the status column to determine which VLF is still active. That is as far as the log file can be shrunk...

    Shrink the file, then do another transaction log backup - repeat until you have shrunk the file to the desired size. Ideally, you want to get rid of the secondary log file as it is not needed.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jpotucek (4/17/2013)


    I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)

    Just a note - a SQL Server database cannot have 2 log files, only one.

    I would:

    1. change the DB recovery mode to simple;

    2. shrink the log file;

    3. change the DB recovery mode back to full;

    4. run the full backup for it;

    5. run the regular tlog backups (every hour, for example) after that;

  • A full backup will not do anything with the log files.

    I know. The full backup is in case shrinking the logs causes any problem that you might want to back out of. Unlikely, yes, but I'd rather have a backup and not use it than need a backup and not have one.

    Just a note - a SQL Server database cannot have 2 log files, only one.

    It can have as many as you like. That's not to say it should, though.

    change the DB recovery mode to simple

    You're the second person that's suggested that. I can't think why you'd want to. Please will you explain?

    John

  • simon.murin (4/17/2013)


    Jpotucek (4/17/2013)


    I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)

    Just a note - a SQL Server database cannot have 2 log files, only one.

    Good Catch

    I would:

    1. change the DB recovery mode to simple;

    2. shrink the log file;

    3. change the DB recovery mode back to full;

    4. run the full backup for it;

    5. run the regular tlog backups (every hour, for example) after that;

    why do you want to suggest changing recovery model when shrink command works in full recovery?

    Regards
    Durai Nagarajan

  • simon.murin (4/17/2013)


    Jpotucek (4/17/2013)


    I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)

    Just a note - a SQL Server database cannot have 2 log files, only one.

    You'd think that were true, but it's not. SQL Server databases can have multiple log files, however there's not usually a good reason for doing so. It writes to them sequentially, not striping, so there are no performance gains. The only reason I can think when it might be handy is if you have several small drives and need the space but can't reconfigure the drives.

    The first thing to do therefore might be to get rid of one of the log files.

    After that, the thing to bear in mind is VLFs. A log file that is shrunk and then allowed to grow by itself can result in far too many VLFs, which can affect performance. See this article by Kimberly Tripp[/url] for some excellent advice.

    Duncan

Viewing 15 posts - 1 through 14 (of 14 total)

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