Solved

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

Posted on 2010-11-29
16
561 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
Comment Utility
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
Comment Utility
>>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
Comment Utility
@acperkins

What's the difference? Do i need to backup the log? Should I have it in Simple Mode?
0
 
LVL 10

Expert Comment

by:Humpdy
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
>>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
Comment Utility
And if you required to have point-in-time restore, are you prepared to schedule frequent Transaction Log backups?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
The goal is to keep the space usage as low as possible
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

11 Experts available now in Live!

Get 1:1 Help Now