Solved

excessively large sql server log file

Posted on 2013-01-08
4
226 Views
Last Modified: 2013-01-09
I have a small mdf with a large ldf file. How can I reduce the size of the ldf. Some type of defrag or reindex or compact procedure. Please explain.
0
Comment
Question by:glenn_r
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 210 total points
ID: 38756909
Have just gone through a lot of this discussion in this question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27988907.html

Have a read of these two blog posts:
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

It's all explained in there.

Make sure you're taking regular transaction log backups.

To fix (in shorthand):

Take full backup
Take log backup
Shrink log with dbcc shrinkfile
Grow again in chunks to appropriate size for your workload.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38758135
#1, stay away from Shrinking your logs.
#2, go to the following article.
http://kb.cffcs.com/Main.asp?irid=193&Type=Article

Carrzkiss
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38758726
I'm sorry, but I have to disagree with carrzkiss and the site he's directed you to.  Setting your databases to simple for a production database destroys the ability to perform point in time recovery.

Letting the system recreate your log file in this way means you have a small logfile with default sizing and then if you're in full recovery mode, as it grows you end up with log fragmentation and too many VLF's.

Shrinking log files is an acceptable practice.  Shrinking data files is something you want to stay away from.
0
 
LVL 30

Expert Comment

by:Wayne Barron
ID: 38759554
@sjwales
Yes, you are correct, I mis-spoke when I stated not to shrink the log files, I was thinking about the database itself.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now