Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server Delete, Shrink, Eliminate Transaction Log .LDF File

Delete, Shrink, Eliminate Transaction Log .LDF File

In some case, the Microsoft SQL Server Database Transaction Log (.LDF) file becomes very big even ton of Gigabytes. It’s wasting lot of disk space and causing some problems if you want to backup and restore the database.

To face with this issue, in this tutorial, you can apply either of following methods. I’m on Microsoft SQL Server 2008 but it also be applied for Microsoft SQL Server 2005.

1. Shrink the transaction log (.ldf) file

By using this method, you can shrink the log to the desired size, ex: 1MB

+ Right-click on the database you want to shrink => Tasks => Shrink => Files

Microsoft SQL Server 2008 Shrink

Microsoft SQL Server 2008 Shrink

+ On Shrink File box, select Log on File type drop down list and select Reorganize pages before releasing unused space then set size for the log file, ex: 1 MB as picture below:

Microsoft SQL Server 2008 Shrink Set Size Log File

Microsoft SQL Server 2008 Shrink Set Size Log File

+ Click OK and you’re done.

The good point of this method is you can shrink at the right time but sometime, it doesn’t work especially with Microsoft SQL Server 2005. Let’s try the second method below in case it’s does not work.

2. Completely delete the log file

The main idea of this method is it will delete the big log file and create a new log file with the minimum size.

Follow steps below:

1. Detach the database

Right-click on the database => Tasks => Detach…

Microsoft SQL Server 2008 Detach

Microsoft SQL Server 2008 Detach

2. Delete the big log file

This step will completely erase it from your hard drive. However, from my suggestion you should back up the log file somewhere, ex: rename it from the file location (my case is: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA)

Microsoft SQL Server 2008 Data Folder

Microsoft SQL Server 2008 Data Folder

3. Attach the database again

As we deleted the log file, so we do attach the database without the log file. Don’t worry, SQL Server will create a new log file for you which will be of the minimum size. That’s perfect!

+ Right-click Databases => Attach…

Microsoft SQL Server 2008 Attach

Microsoft SQL Server 2008 Attach

+ On Attach Database box, click Add…

Microsoft SQL Server 2008 Attach Add

Microsoft SQL Server 2008 Attach Add

+ Browser to the database (.mdf) file then click OK

Microsoft SQL Server 2008 Browser MDF

Microsoft SQL Server 2008 Browser MDF

+ Select the log (.ldf) file then click Remove

Microsoft SQL Server 2008 Attach Remove Log

Microsoft SQL Server 2008 Attach Remove Log

+ Finally, click OK and you’re done.

Note: after finished steps above, you should double check in your Data folder to see if there is a new log file created. If yes, let delete the big log file which was backup in step 2, remember?

Apr 1, 2011Hoan Huynh
Share Page On Facebook With Thumbnail Featured ImageDymanic Crm Error 401 Unauthorized When Create Or Retrieve Account And Contact
You Might Also Like:
  • MSSQL Export A Table Data To CSV
  • Export/Import Data From Local Database To Remote Database With MS SQL Server 2008
  • PHP Delete File Function
  • How To Delete Application In Facebook Developers
  • Remove/Delete your saved passwords in IE, Firefox, Google Chrome
  • WordPress Delete Unused Post Tags By SQL Command
  • Install ASP On Microsoft Windows Server 2008 IIS 7.0 and IIS 7.5
  • Delete Bulk Draft And Trash WordPress Posts Includes All Tags, Comments, Meta Fields And Terms Associated
  • Delete WordPress Revision Posts, Its Relationships And Meta Data
  • Auto Delete Old IIS Logs, FTP Logs, SMTP Logs In Windows
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

Gallery11 years ago SQL Server.LDF File, .MDF File, attach database, detach database, Transaction Log17,656
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,459 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,839 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,656 views
JQuery Allow only numeric characters or only alphabet characters in textbox
14,994 views
C# Read Json From URL And Parse/Deserialize Json
11,722 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development