Solved

SQL Log File

Posted on 2011-03-09
4
301 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - stored prodecure with execution plan make faster 24 115
the whoisactive update 12 39
sql Total query 2 18
Alter a column in sql 34 0
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach 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.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

26 Experts available now in Live!

Get 1:1 Help Now