Solved

Transcation Log filing the entire hdd.

Posted on 2013-11-18
7
287 Views
Last Modified: 2013-12-23
Hi,

I have SQL database that is 500mb, however the transaction log file has now grown to 80gig

What is wrong here and how can it be resolved?

Many Thanks.
0
Comment
Question by:andyw27
7 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 39656792
My guess is that you are running in Full Recovery Mode and have never backed up your log file.

I wrote an article on how to deal with this that should help:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

Unless you've got space on your backup device to backup that log file now (In order to shrink it) - and the time to run the backup - you may have to do a truncate of your logfile followed immediately  by a full backup.

However, that's a risky proposition because failure between truncate and completion of backup means you're in a bit of trouble - but a 500MB database won't take long to backup.

Have a read of the article, check out the links in it and come back with any questions.
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39656801
My guess is that your database is in full recovery model and you are not backing up the transaction logs.  Chances are, if you're not backing up the transaction logs currently, you're not concerned about point in time recovery (which is the main point of full recovery model).  If I am correct about not needing point in time recovery, then you'll want to backup the transaction log, change the database to simple recovery model and then shrink the log file.  If you do want point in time recovery, you'll still want to backup the transaction log, shrink the log file, and then set up a backup schedule for backing up your transaction logs in addition to your full backups.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39656814
I went back to edit my initial reply but Padawan had replied in the mean time.  

Adding on to the above:
The option to truncate your logfile is a dangerous one.  If you are in the least concerned about point in time recovery, don't do it - it should be a last gasp effort.

If you've got the space on  your backup device, I'd advise running the transaction log backup.  Then you can shrink the log file.

Then, make sure to include transaction log backups as a part of your regular backup cycle.  This marks the space in the log file as reusable so the log file won't grow that large again.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39656829
Sorry about that, by the way!  I didn't notice you had posted until after the fact.  Cheers!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39657575
>> What is wrong here and how can it be resolved? <<

It depends; most likely it is as described above, but just in case ...

Naturally replace "your_db_name" with your actual db name in the commands below.

1) First, verify that there are no open transactions against the db.

USE your_db_name
DBCC OPENTRAN

2) If you get the expected return message:
"No active open transactions."
    Then run these commands:


USE your_db_name
CHECKPOINT
ALTER DATABASE your_db_name SET RECOVERY SIMPLE
DBCC SHRINKFILE ( 2, 500 )

-- review output from this command to verify that the log file did actually shrink to 500MB
EXEC sp_helpfile

BACKUP DATABASE your_db_name TO DISK = 'x:\full\path\to\full\db\backup\your_db_name.bak'
0
 
LVL 1

Expert Comment

by:BradySQL
ID: 39658422
It might be worth noting that it may take more than one transaction log backup before you see the file shrink, this has to do with the way that the sql logs work. If you would like I can go into more detail, but I think it might be worth just saying, back up your log a couple times before you panic over it not shrinking.
0
 
LVL 3

Expert Comment

by:Sagir87
ID: 39658631
Hi,

First Perform transaction log backup:

backup log  to disk='OS Path.trn'

After performing transaction log backup of database which is having 80 Gb of log files i.e. ldf and after that shrink the log files.

Using SQL management studio
Right click on database
go to task then
go to Shrink then
go to Files

then after that from drop down menu select database log files not data files because Microsoft recommends do not shrink data files i.e. mdf but you can shrink log files i.e. ldf.

OR you can command in sql server :

USE UserDB;
GO
DBCC SHRINKFILE (userdb_log, 7);
GO

After performing above activity once again perform transaction log backup do not worry now log backup size is small and one more time shrink the log files of database and see the results.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

919 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

24 Experts available now in Live!

Get 1:1 Help Now