Solved

SQL Log File

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to install/upgrade the Blitz responder kit 8 42
Sql Query Datatype 2 23
Generate Scripts of Schema/Data with "WHERE" clause 6 60
Count with a subquery showing details 10 41
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

805 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