Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Databases»SQL Server»How to Shrink SQL Server Transaction Log Files using DBCC SHRINKFILE

    How to Shrink SQL Server Transaction Log Files using DBCC SHRINKFILE

    By RahulAugust 13, 20192 Mins Read

    Sometimes SQL Server logs files acquire a large space on the disk. In that case, we need to reduce the size of the log file to claim some space. But truncating or reducing database log file in SQL Server 2008 can be quite difficult. It also has its own issues.

    Advertisement

    This article contains a set of commands to Shrink Log Files of SQL Server database.

    Generally this command is I prefer to take a backup of the database before executing below command for a safer side

    Shrink SQL Server Transaction Log Files

    First of all, check the actual name of log files stored in SQL server records using the following SQL query. Execute these queries in using SQL Server management studio query browser.

    USE DATABASE_NAME;
    GO
    
    SELECT FILE_NAME(2);
    GO
    

    Now, use the DBCC SHRINKFILE SQL command to shrink log files. The database must be running in SIMPLE recovery mode. Open the query windows in SQL Server management studio and run the following command. You

    Make sure to change DATABASE_NAME with actual database name and LOG_FILE_NAME with log file name found in the above step. The below example query will shrink the log file to 1000MB.

    USE DATABASE_NAME;
    GO
    
    ALTER DATABASE DATABASE_NAME
    SET RECOVERY SIMPLE;
    GO  
    
    DBCC SHRINKFILE (LOG_FILE_NAME, 1000);
    GO  
    
    ALTER DATABASE DATABASE_NAME
    SET RECOVERY FULL;
    GO  
    

    All done. Now check the size of the log files, I hope you will get the expected results.

    log mssql shrink SQL SErver
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    How to Backup SQL Server Database

    How to Backup SQL Server Database

    How to Restore SQL Server database

    How To Restore SQL Server Database

    How to Rename SQL Server Database

    How to Rename a SQL Server Database

    View 1 Comment

    1 Comment

    1. import on January 8, 2014 8:45 am

      I could not resist commenting. Very well written!

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • How to Split Large Archives in Linux using the Command Line
    • System.out.println() Method in Java: A Beginner’s Guide
    • Split Command in Linux With Examples (Split Large Files)
    • Test Your Internet Speed from the Linux Terminal
    • 11 Practical Example of cat Command in Linux
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.