Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

excessively large sql server log file

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
glenn_r
Asked:
glenn_r
  • 2
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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
 
Wayne BarronCommented:
#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
 
Steve WalesSenior Database AdministratorCommented:
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
 
Wayne BarronCommented:
@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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now