How to manage SQL Log File .ldf

My problem is having issue with SQL Server 2014 Database and its size growing and consuming Hard disk is almost full, Only 50GB space is left in 1TB Hard drive, and SQL database log (.ldf) file is consuming 800GB space and continue increasing, We can Shrink the database-log but there are several blogs which says it effects the server performance and data loss, or we can increase the space of the hard disk, Please advise.

Any Help or suggestions much appreciated.

Replay

Although the question Why Does the Transaction Log Keep Growing or Run Out of Space? provides lots of information on transaction log management, it does not mention the main performance problem of Virtual Log Files, which I will address below.

Since your question is about managing size and performance of the transaction log, I'll write about each subject individually.

The transaction log records all transactions and database modifications made by each transaction, and several maintenance actions have an affect on the space requirements of the transaction log. When managing the size of the transaction log, you will need to schedule all maintenance activities with an understanding of how they will affect growth and how they won't. Maintenance activities include transaction log backups, updating statistics, rebuilding indexes and other regularly scheduled activities. Your transaction log contains active virtual log files and inactive virtual log files in on single physical file. Active log files contain transactions that have not been backed up and inactive log files are available for reuse and are mainly the free space in the log file. Generally, when pages in the database change, those changes are recorded in the transaction log. During an operation like rebuilding all indexes, pages will change at a rapid pace and could prompt the database engine to autogrow the log file. Autogrowths actions are recorded in the default trace. I've experienced rapid growth due to index rebuilding, but I had the space to accommodate so I didn't make any changes. You could increase the frequency of the transaction log backups during index rebuilds, assuming you use full recovery model, and distribute the index rebuilds over time so that the growth is mitigated.

I've experienced poor performance in log files to the point where some of the applications developed ineternally were slowing noticibly and timing out. It was due to excessive number of small virtual log files. The size and number of virtual log files contined in one physical file is directly related to the autogrowth settings. To check if you have excessive VLFs, run DBCC Loginfo. If the resulting number of records is in the hundreds or thousands, then perform the following procedure:

  • Change the autogrowth settings to something reasonable. It's difficult to make a recommendation in your case because an 800GB log file is highly unusual for a 96GB database. It needs to be a value that is large enough so that the database engine only creates a minimal amount of VLFs. I would try 500MB to 1GB.
  • Shrink the file as small as possible
  • Reinitialize the file. Usually this step is to reinitialize the file back to it's original size. If you have no idea what size to reinitialize it, try 50GB.

Allowing it to grow without reinitializing it will allow the VLF issue to occur again and contributes to higher IO because each autogrowth of the log file needs to be completely written out with zeros before it can be used. This is why shrinking the log file causes poor performance.

Category: sql Time: 2016-07-28 Views: 0

Related post

  • How best to maintain SQL log file sizes 2013-04-24

    I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery. Right now, I'm taking a full backup of the databases and logs every da

  • Transaction Log file .ldf is too small 2012-10-14

    Current database is almost 200GB however the transaction log file is only 4.1GB. I am worried if this influences the performance of databse and how could it be solved. When I do this query DECLARE @DatabaseName VARCHAR(50) ; SET @DatabaseName = 'dbna

  • Log File(.ldf) in Primary Server is becoming huge in LOG SHIPPING 2016-07-21

    Even after log shipping is configured, the log file (ldf) is not getting truncated. The ldf file grows high and high. And if i take a log backup manually in primary server, then the log shipping chain gets breaks and restore job will not be successfu

  • How to use Binary Log file for Auditing and Replicating in MySQL? 2010-04-07

    How to use Binary Log file for Auditing in MySQL? I want to track the change in a DB using Binary Log so that I can replicate these changes to other DB please do not give me hyperlinks for MySQL website. please direct me to find the solution EDIT I h

  • How to specify the log file for memcached on RHEL / CentOS 2010-12-02

    I'm running memcached 1.4.5 on RHEL5.5. I installed this using yum which has installed the standard memcached script in /etc/init.d and the configuration file in /etc/sysconfig/memcached: PORT="11211" USER="memcached" MAXCONN="102

  • How to tail a log file when removing lines (not appending) of the file? 2014-01-27

    I want to know how to tail a log file when lines in the files are updating(appending and removing)? --------------Solutions------------- As Atari911 said, you can use watch along with cat to do this. Use watch cat <filename> this will output the con

  • How to generate separate log files for each and every file processed using java log4j? 2016-01-29

    I need to get a separate log file for each input file processed. I have written an app using java,log4j,javamail api to validate the xml.it takes input ini files(to get the path of xml file to be validated).once a file is processed, it should generat

  • how to run sql batch file (.sql) using pg module ruby 2016-02-11

    i have installed ruby and pg module in centos machine, able to connect db too and can run sql statement, but don't know how to run .sql batch file ex. masterupdate.sql code- require 'pg' conn = PG::Connection.connect_start('x.x.x.x,5432,nil ,nil, nil

  • How to manage SQL Server Log file? 2012-01-06

    I have an application build on SharePoint 2007 platform, and currently I encounter a common issue where the Database Log file growth rapidly and fill up the hard disk space. In my situation, I wanted to remain the recovery model as FULL because in fu

  • Safely deleting a SQL Server log file (.LDF) 2012-02-23

    I can make sure my app is in a consistent state. I can rollback all the uncompleted transactions if any (just in case) it's ok I can DETACH the database What do I need the log file for after that? I'm particularly talking about a "highly controlled&q

  • how to consolidate multiple log files into one ldf file in sql2000 2010-06-03

    I'm in the process of copying databases from SQL 2000 to a 2008 instance on another server using DETACH, copy windows file to 2008 server, then finally ATTACH. I've come to a database where the LOG file is in 2 windows files: name fileid filename siz

  • How to reduce the log file size without shrinking it in SQL server 2011-05-13

    In SQL server, how do you reduce the log files size without (DBCC) shrinking it. I know shrinking the log file will free up some space, but will also cause fragmentation. Doing a checkpoint in simple mode or backing up the transaction log in full mod

  • SQL Server 2012 mirror setup - how to reduce transaction log files 2015-06-25

    This question already has an answer here: Why Does the Transaction Log Keep Growing or Run Out of Space? 4 answers We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirro

  • How can I view log file more then 1GB 2010-09-24

    I have a Linux server with 512MB RAM and no SWAP. How can I view 1GB file? --------------Solutions------------- You can use split. Another useful tool in addition to head and tail is split. Chop that 1GB file into a bunch of smaller files. I think "l

  • SQL log file growing rapidly 2011-04-11

    I encountered SQL Database log file growing rapidly and filled up the Hard Disk space, the growth was approximately 190MB per day. there is Sharepoint running in the server, how can I check what causes the log files size increase? could it because of

  • How to shrink 40GB LOG file 2011-04-29

    I have a problem that is my log file in SQL Server 2008 is grown to 40Gb and I wanted to shrink it. The query I am using is ALTER DATABASE DatabaseName SET RECOVERY SIMPLE use DatabaseName GO CHECKPOINT GO DBCC SHRINKFILE(transactionloglogicalfilenam

  • Maintaining SQL Log Files 2012-05-24

    I get an email today stating that we have a log file that's grown to 278GB, and asking if anyone is maintaining it. I already know the answer to that is no because we don't have a dba (other than myself. I know a fair amount about sql, but I'm a deve

  • How to group SQL Server files for restoring database 2012-09-23

    I'm using SQL Server (2008 R2 in this case) and I am backing up my databases into backup devices (one per database). Every Sunday the device is overwritten with a new full backup, every night a differential backup is added and every hour a transactio

  • How to prevent SQL Log from being too full 2012-12-17

    I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even though there are a lot of transactions, the actual

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 1.296 (s). 13 q(s)