Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

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

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
engineroom
Asked:
engineroom
  • 7
  • 6
  • 2
  • +1
1 Solution
 
yatin_81Commented:
Use this code :
ALTER DATABASE JSohamRSBL  
SET RECOVERY SIMPLE;  
  
  
DBCC SHRINKFILE (LogicalFileName, 2)  
  
ALTER DATABASE DatabaseName  
SET RECOVERY FULL;

Open in new window

0
 
Anthony PerkinsCommented:
>>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
 
engineroomAuthor Commented:
@acperkins

What's the difference? Do i need to backup the log? Should I have it in Simple Mode?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HumpdyCommented:
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
 
engineroomAuthor Commented:
@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
 
HumpdyCommented:
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
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
And if you required to have point-in-time restore, are you prepared to schedule frequent Transaction Log backups?
0
 
engineroomAuthor Commented:
I have limited space on my hard drive and there are many databases that are blowing up. We do backup manually.
0
 
Anthony PerkinsCommented:
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
 
engineroomAuthor Commented:
What do you mean by Executing a series of checkpoints? Should the logfile name be the full path
'c:\program files\...'?
0
 
Anthony PerkinsCommented:
>>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
 
engineroomAuthor Commented:
If i do change it to simple mode, do checkpoints matter if i just go in there and backup the database?
0
 
Anthony PerkinsCommented:
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
 
engineroomAuthor Commented:
The goal is to keep the space usage as low as possible
0
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now