Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excessively large sql server log file

Posted on 2013-01-08
4
Medium Priority
?
235 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 23

Accepted Solution

by:
Steve Wales earned 840 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 31

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 23

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 31

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

972 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