Solved

MS Sql Server Huge Log file.... how do i shrink it?!?

Posted on 2010-11-29
16
578 Views
Last Modified: 2012-05-10
Hey all, i have a sql database and the log file is hitting 6.5 gigs. Don't know why but there's no reason it should be that huge. How do i stop the log file from growing or shrink it or maybe even get rid of it. Thanks all

er
0
Comment
Question by:engineroom
[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
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 2

Expert Comment

by:yatin_81
ID: 34232845
Use this code :
ALTER DATABASE JSohamRSBL  
SET RECOVERY SIMPLE;  
  
  
DBCC SHRINKFILE (LogicalFileName, 2)  
  
ALTER DATABASE DatabaseName  
SET RECOVERY FULL;

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34232937
>>MS Sql Server Huge Log file.... how do i shrink it?!?<<
Before you do anything, please stop and think why it is so big, before you cause any damage?  If the answer is you are using the Full Recovery Model and you have never bothered to backup the Transaction Log.  Then now may be the time to start.  If you are unable or unwilling to backup the Transaction Log, then perhaps you should consider changing the Recovery Model to Simple.
0
 
LVL 3

Author Comment

by:engineroom
ID: 34233053
@acperkins

What's the difference? Do i need to backup the log? Should I have it in Simple Mode?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 10

Expert Comment

by:Humpdy
ID: 34233652
you should never shrink your log file.

Difference between full mode and simple mode, is that in full mode, you can recover your database to a point in time. You can do full backups and transaction log backups, which you can't do in simple mode.
Backing up your transaction log will flush out committed records, and reduce the size.

Note: You need to do a full backup first, before you can do a transaciton log backup.
Here's a brief overview of the differences in recovery models.

http://msdn.microsoft.com/en-us/library/ms189275.aspx
0
 
LVL 3

Author Comment

by:engineroom
ID: 34234370
@Humpdy

I did a full back up and then a transaction log backup but the log is still at 6.5 gb. I thought that backing it up would clear all the junk out... what next?

er
0
 
LVL 10

Expert Comment

by:Humpdy
ID: 34234383
can you do the following,
what's the initial size of the transaction log, have a look in the properties of the database under files.
also, can you do
DBCC LogInfo
and
DBCC Opentran
and paste the info here
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34235651
>>I did a full back up and then a transaction log backup but the log is still at 6.5 gb. I thought that backing it up would clear all the junk out... <<
That is because it does not shrink the file.  It merely make the space available.  Why don't you tell us what is your goal? Do you need point-in-time restore?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34235657
And if you required to have point-in-time restore, are you prepared to schedule frequent Transaction Log backups?
0
 
LVL 3

Author Comment

by:engineroom
ID: 34238795
I have limited space on my hard drive and there are many databases that are blowing up. We do backup manually.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34244554
So it sounds like you cannot schedule frequent Transaction Log backups, which makes the default Full Recovery Model not feasible.  This is what you need to do:
1. Change the Recovery Model for the database(s) to Simple.
2. Execute a series of CHECKPOINT
3. Shrink the Transaction Log to an appropriate size.  Do not set it too low, this is a very bad idea.
DBCC SHRINKFILE('Your Logical Transaction Log File Name Goes Here', x)   -- Where x is in MB
0
 
LVL 3

Author Comment

by:engineroom
ID: 34248296
What do you mean by Executing a series of checkpoints? Should the logfile name be the full path
'c:\program files\...'?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34248708
>>What do you mean by Executing a series of checkpoints? <<
As in executing the following in SSMS multiple times:
CHECKPOINT

>>Should the logfile name be the full path <<
No.  I am talking about the Logical and not the physical name.  If you are still unsure then checkout the sys.master_files system table.  It shows the name as well as the physical name for all the databases.

0
 
LVL 3

Author Comment

by:engineroom
ID: 34248815
If i do change it to simple mode, do checkpoints matter if i just go in there and backup the database?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34250788
Again, what is your goal?  If you are attempting to get your Transaction Log file under control and you are not prepared to schedule frequent Transaction Log backups and you know the consequences of running under the Simple Tecover Model, then follow the steps I stated here http:#a34244554.

This is should be a one time task and should not be scheduled.
0
 
LVL 3

Author Comment

by:engineroom
ID: 34252511
The goal is to keep the space usage as low as possible
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34254301
Then as stated, if you are unable to make frequent Transaction Log Backups then follow the steps I outlined here http:#a34244554 just once to get the file down to a manageble but appropriate size and then you can forget about it.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 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