?
Solved

Transaction Log file is HUGE

Posted on 2014-03-25
9
Medium Priority
?
400 Views
Last Modified: 2014-03-25
I noticed that the data section of my server is getting more full.  When I looked at the data file for the SQL server, it was roughly 1.1GB   However, I noticed the Transaction Log File is 107 GB!  

What is the transaction log file?  Why does it get so huge?  Do I need the information and can I shrink it?

I am afraid to do anything to it as I do not want to cause harm to the server...
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39954406
It's probably in "FULL" recovery mode and has never been backed up.

You can indeed shrink it.

Set the db to simple and shrink the log file.

For example:

USE <db_name>

--find and copy/save the logical name of log file using command below;
--the first column is the logical file name
EXEC sp_helpfile

ALTER DATABASE <db_name>
    SET RECOVERY SIMPLE

DBCC SHRINKFILE ( 2 )

ALTER DATABASE <db_name>
    MODIFY FILE ( NAME = <logical_file_name_for_log_file>, SIZE = 100MB, FILEGROWTH = 20MB )
0
 
LVL 35

Expert Comment

by:Bembi
ID: 39954421
...and make sure to take backups in the future....
If you do not have a backup solution, use mainenance plans to backup the databases to avoid th eissue in the future.
0
 

Author Comment

by:al4629740
ID: 39954436
I do have a maintenance plan that backs the database up to another folder.  What is the purpose of the log file?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39954491
SQL requires a log file to insure database consistency.

You can also use the log file to recover your database to a point in time if you want to.  In order to do that, you must put the db in FULL (or BULK-LOGGED) recovery mode and you must backup the log files.

If you don't need or want the forward recovery capability, you can leave the db in SIMPLE recovery mode.  But, if the db is lost or damaged, you can only recover to the point of a full or differential backup.


An example is probably clearer.

(1) Db "Db1" is in "FULL" mode; full backup is at 8PM, log backups every half hour.
If the db is lost/destroyed at 3:45AM, you could restore the backup from 8PM, and apply the logs from 9PM through 3:30AM to recover the db as it looked at 3:30AM.  All data added/changed past 3:30AM is lost.

(2) Db "Db1" is in "SIMPLE" mode; full backup is at 8PM, no log backups.
If the db is lost/destroyed at 3:45AM, you could restore the backup from 8PM.  All data added/changed past 8PM is lost.
0
 

Author Comment

by:al4629740
ID: 39954554
So basically you think my db is in FULL mode.  How can I tell?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39954607
SELECT
    name, recovery_model_desc, log_reuse_wait_desc,
    is_cdc_enabled, snapshot_isolation_state_desc, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<your_db_name>'
0
 

Author Comment

by:al4629740
ID: 39954654
name	recovery_model_desc	log_reuse_wait_desc	is_cdc_enabled	snapshot_isolation_state_desc	is_read_committed_snapshot_on
CAPRegistration	FULL	LOG_BACKUP	0	OFF	0

Open in new window

0
 

Author Comment

by:al4629740
ID: 39954656
Is that a default setting or did I set that up?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39954757
The "model" db controls the defaults.  If model is in full mode, all new dbs will be in full mode (unless explicitly set otherwise).

The results confirm that the db is in FULL mode, and that the reason the log isn't being truncated (which in SQL-speak means "reused", not physically shrunk) is because it hasn't been backed up.

There's absolutely no point to a log backup now, as it would be far too large and cover too much time to be useful.  So use the steps above to put the db in simple mode, then shrink the log and re-grow it (to clear the huge number of VLFs).

Then, if you want, you can set the db back to full mode and put log backups in place.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

765 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