Solved

SQL Log File

Posted on 2011-03-09
4
316 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
  • 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:ewangoya
ewangoya 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 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