Solved

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

Posted on 2010-11-29
16
569 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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