Solved

SQL Log File

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 56
How can I use this function? 3 35
how would you excute a sproc on another database on the fly 9 35
Database Availability Group Distribution 9 51
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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