[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2010-11-29
16
Medium Priority
?
592 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.
Suggested Courses

834 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