Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to shrink or clear the transaction log file ?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 311
    Comment on it

    Many times the large size of transaction log file (.ldf) in Microsoft SQL is too big which leads to performance issues and loss of valuable disk space.Therefore it's imperative to periodically do database maintenance.

    In order to clear or shrink the .ldf file, you should first take backup and then follow the below process:

    USE AdventureWorks2008R2;
    GO
    -- Truncate the log by changing the database recovery model to SIMPLE.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY SIMPLE;
    GO
    -- Shrink the truncated log file to 1 MB.
    DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
    GO
    -- Reset the database recovery model.
    ALTER DATABASE AdventureWorks2008R2
    SET RECOVERY FULL;
    GO
    

    Ref : https://msdn.microsoft.com/en-us/library/ms189493.aspx

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: