Thursday, March 4, 2010

My SQL LDF file is so big in comparison with the MDF file, what can I do ?

At a lot of customer sites I  have seen situations in which the transaction log file (LDF) is multiple times bigger than the MDF file. This is not a 'normal' situation. In this blog I will explain what you can do if you have an LDF file of your SQL server database which is too big in comparison with the size of your MDF file.



What is the difference between the MDF and LDF file ?
The .MDF file is the primary data file of a SQL database. The .LDF file is the transaction log file of a SQL database. The transaction log is used to guarantee the data integrity of the database and for data recovery. Data integrity is implemented by design and can't be configured by the database administrator. Data recovery is implemented by design and is configurable by the database administrator.

In SQL Server you have 3 different data recovery models: Simple, Full and bulk-logged. Typically, a database uses the Full recovery model or Simple recovery model.

What is the main difference between Full and Simple?
  • Simple: No transaction log backups. Changes since the most recent backup are unprotected. In the event of a disaster, those changes are lost and need to be redone. You can only recover to the end of a backup.
  • Full: Requiers transaction log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For example, prior to application or user error. 

What is in the transaction log in the Simple recovery model ?
All open transactions. This means not committed transactions. This is called the active part of the transaction log. At the moment a transaction is committed it will be truncated from the transaction log. Log truncation automatically frees disk space for reuse by the transaction log. The biggest size of the transaction log will be the size of all open transactions at one moment. Therefor in a 'normal' SQL production database the transaction log file will never get very big.

What is in the transaction log in the Full recovery model ?
All committed transaction (inactive part) and open transactions (active part). The log records cannot be truncated untill all its log records have been captured in a transaction log backup. The log is truncated when you backup the transaction log.

What can we do to lower the size of the transactional log file (LDF)?
It depends on the configured recovery model of your database, what you can do. The recovery model of your database can be found in the properties of the database:

or via SSMS:
Open a query window and select the database.

USE
SELECT Recovery_model_desc FROM sys.databases WHERE Name = db_name()


What to do if your recovery model is: FULL?
Check if you are making transactional backups on a reguler time interval. If you only make full backups your transaction log will always grow and will never be truncated. In case you never want to lose data in case of a application or user error. Start making transactional log backups. If a database restore to the latest full backup is acceptable, you can switch the recovery model of your database to simple. Before switching the recovery model of your database please read next document: Considerations from switching the recovery model of your database. After switching to the recovery model Simple , you can shrink the log file. See the end of this blog how to do this.


What to do if your recovery model is: Simple?
Shrink the log file.


To set the recovery model to Simple you can run next query:
ALTER DATABASE
SET RECOVERY SIMPLE

To set the recovery model to FULL you can run next query:
ALTER DATABASE
SET RECOVERY FULL

How many free space is available in my log file?
  • Select the database in the Object Explorer
  • Point to Tasks, Point to Shrink and then click Files.
  • Select File Type: Log
  • Currently allocated: The current size of your log file
  • Available free space: The free space in your log file
To shrink the log file:
  • Select the database in the Object Explorer
  • Point to Tasks, Point to Shrink and then click Files.
  • Select File Type: Log
  • Select Release unused space
  • Press OK to start the shrink process. If you do not want to shrink the log file press the cancel button.

33 comments:

Alexis said...

I seldom work with sql server and files too. But once I couldn't use my files,because of they were corrupted. And fortunately I incidentally entered in the Internet and observed there - sql server repair database. The utility resolved my issue for a minute and free of charge as I bore in mind.

Raj said...

Hello Dear,

i had many confusion about mdf, ldf ndf files of sql server database. after reading your article, i am mostly satisfy.

I have also written article about database recovery software.

נמרוד כנען said...

Nice articcal

Andrei said...

This article helped me in shrinking the log file size. Very nice indeed. Helped me a lot... I recovered 7Gigs of space. Thanks!

All mankind love a lover. said...

i have a database my mdf fils is 139 mb & ldf file is 29.7 gb I've taken transaction log backup and then shrink database file it is reduced but only 1 gb what should i do to reduce the size of ldf file (gmailbackup@sify.com)

All mankind love a lover. said...

i have a database my mdf fils is 139 mb & ldf file is 29.7 gb I've taken transaction log backup and then shrink database file it is reduced but only 1 gb what should i do to reduce the size of ldf file (gmailbackup@sify.com)

Unknown said...

HI André,

I also wrote an article on this topic. I cover all the possible causes of growing transaction log file as well as possible solutions. Please have a look on this article: Decrease Transaction Log File Size

Unknown said...

All mankind love a lover,
Alter your database to simple recovery model and then shrink LDF to 1.

ALTER DATABASE SET RECOVERY SIMPLE

DBCC SHRINKFILE(,1)

Unknown said...

All mankind love a lover,
Alter your database to simple recovery model and then shrink LDF to 1.

ALTER DATABASE "DBNAME" SET RECOVERY SIMPLE

DBCC SHRINKFILE("Logical Name of your Logfile",1)

Unknown said...
This comment has been removed by the author.
Data Science Training said...

I truly like only reading every one your web logs. Simply desired to in form you which you simply have persons such as me that love your own work out. Absolutely an extraordinary informative article. Hats off to you! The details which you have furnished is quite valuable. Tableau Course in Bangalore

Cyber Security Course In Bangalore said...


Very wonderful informative article. I appreciated looking at your article. Very wonderful reveal. I would like to twit this on my followers. Many thanks! .

Cyber Security Course In Bangalore

Mike Johnson said...

Maybe you can make short video about it for tiktok? On this site https://soclikes.com/buy-tiktok-likes you can get tiktok likes

Data Science Courses In Bangalore said...


I'm really thankful that I read this. It's extremely valuable and quite informative and I truly learned a great deal from it.

Data Science Course In India

Data Science Course Syllabus said...


Nice to be seeing your site once again, it's been weeks for me. This article which ive been waited for so long. I need this guide to complete my mission inside the school, and it's same issue together along with your essay. Thanks, pleasant share.

Data Science Course Syllabus

Huongkv said...

Đặt vé máy bay tại Aivivu, tham khảo

mua ve may bay di my

đăng ký bay từ mỹ về việt nam

vé máy bay đà nẵng nha trang

vé máy bay nha trang phú quốc

giá vé máy bay đi Huế

Data Science in Pune said...

I found Habit to be a transparent site, a social hub that is a conglomerate of buyers and sellers willing to offer digital advice online at a decent cost.
Data Science Course in Pune

Data Analytics Course in Bangalore said...

I like to see websites that include the price of free shipping from the excellent helpful resource. I really enjoyed reading your post. Thank you!

Data Analytics Course in Bangalore

Unknown said...

I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
data science course

Best Data Science Courses said...

It is late to find this act. At least one should be familiar with the fact that such events exist. I agree with your blog and will come back to inspect it further in the future, so keep your performance going.

Best Data Science Courses in Bangalore

Data Analytics Course said...

I wanted to leave a little comment to support you and wish you the best of luck. We wish you the best of luck in all of your blogging endeavors.
Data Analytics Course in Bangalore

360DigiTMG said...

Wonderful blog. I am delighted in perusing your articles. This is genuinely an incredible pursuit for me. I have bookmarked it and I am anticipating perusing new articles. Keep doing awesome!
data analytics training in hyderabad

Data Science said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one.
Continue posting. A debt of gratitude is in order for sharing.
data science training in gwalior

Data Science said...


Amazingly by and large very interesting post. I was looking for such an information and thoroughly enjoyed examining this one.
Keep posting. An obligation of appreciation is all together for sharing.
data science course in gwalior

patna said...

The data scientists work on the raw data to take the right insights from it for making better decisions to make the business more prosperous.

data science training in patna

Professional Academic Institute said...

Develop technical skills and become an expert in analyzing large sets of data by enrolling for the Best Data Science course in Bangalore. Gain in-depth knowledge in Data Visualization, Statistics, and Predictive Analytics along with the two famous programming languages and Python. Learn to derive valuable insights from data using skills of Data Mining, Statistics, Machine Learning, Network Analysis, etc, and apply the skills you will learn in your final Capstone project to get recognized by potential employers.

Data Science Course in Jaipur


Career Academic institute said...

Get a comprehensive overview of Data Science and learn all the essential skills including collecting, modeling, and interpreting data. Register with Data Science institute Bangalore and build a strong foundation for a career where you will be involved in uncovering valuable information for your organization. Learn Python, Machine Learning, Big Data, Deep Learning, and Analytics to take center stage in Data Science.

Business Analytics Course in Jodhpur

Unknown said...

Simply just, take a log backup and shrunk the log file to the maximum possible. If it's not working, check log_reuse_wait_desc in sys.databases for a particular database. The values, Replication, AVAILABILITY_REPLICA, and TRANSACTION indicate a running transaction waiting at replication, waiting for acknowledgement from secondary or a long-running transaction, respectively, and troubleshoot accordingly. If you need any further clarification or help, email me on krunalpatel155@gmail.com
Thanks,
Krunal

Educational Training and Learning said...

Are you searching for the best institute to start Data Science offline classes that will aid you in career growth, 360DigiTMG offers the best training with expert trainers and a job-ready curriculum. Enroll now!

Data Science Training in Delhi

Swarnalatha said...

"If you are also one of them and want to know what the companies demand from the data scientists to do in their organization, you have come to the right place.data science course in kolkata"

bhodanna56 said...

Your website is really cool and this is a great inspiring article.
data scientist training in Hyderabad .

Ramesh said...

Data Analytics training online course offers you abundant career opportunities. Experience the innovative online training delivered by 360DigiTMG. Get LMS access.
Data Analytics Course in Bangalore

John Albert said...

Hi, I feel that I saw you visited my website in this manner I came to "return the favor".I am attempting to track down things to upgrade my web site!I guess utilizing a portion of your ideas alright!! Halloween Costume