Solved

SQL Log File

Posted on 2011-03-09
4
332 Views
Last Modified: 2012-05-11
Hi i have a relativly small Sql Database its aboy 500 megs however i have recently noticed that i am constantly running out of disk space, i have since noticed that the log file for this database is 38 gigs in size, im not sure why its that big, what options do i have.

I am relativly new to sql so please bear with me.

John
0
Comment
Question by:pepps11976
[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
  • 2
4 Comments
 
LVL 8

Accepted Solution

by:
dba2dba earned 250 total points
ID: 35088025
Please change the Recovery model to SIMPLE and Shrink the log file.

ALTER DATABASE <DBName> SET RECOVERY SIMPLE

use <DBName>
dbcc shrinkfile('<logicalname_of_logfile>',<targetsizeinMB>)

This would reduce the log file size and ensure it does not grow again.

Incase, you needed point in time recovery for the database. You need to set it up to FULL recovery model and configure log backup jobs (I assume you may not need this)

Thanks,

0
 
LVL 8

Assisted Solution

by:dba2dba
dba2dba earned 250 total points
ID: 35088038
http://www.sqlusa.com/bestpractices2005/shrinklog/

The link above has some details.

Thanks,
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 125 total points
ID: 35089369

Shrinking your log files is not a good idea as I'm sure you come across many comments advising against it so I'll not go into the details

Either
1. set your recovery model to simple or
2. schedule regular backups

This should take care of the problem
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 125 total points
ID: 35092491
Additionally consider properly resizing your log.
Please read follwoing article, it may help you understand sizing your log:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

In short, to have your log file smaller and still be able to recover to point in time you need take backups of your t-log. It's good to set optimal initial size of the log so it can be emptied of inactive transactions efficiently.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

636 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