Solved

SQL Server 2005, Large Log Files

Posted on 2014-01-19
13
374 Views
Last Modified: 2014-02-03
My log files are larger than my DB and keep growing.

My drive ran out of space, i have tried everything to shrink, but it's not working, any help would be greatly appreciated.
0
Comment
Question by:FutureDBA-
  • 9
  • 4
13 Comments
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792629
You need to backup the log files before they will get truncated.
You will need to change the recovery mode to simple.
Then when you backup the database you backup the logs.

http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs


Really detailed:
http://support.microsoft.com/kb/317375
http://www.techrepublic.com/blog/the-enterprise-cloud/help-my-sql-server-log-file-is-too-big/#.
0
 

Author Comment

by:FutureDBA-
ID: 39792643
i tried that, it doesnt shrink the log file. the fogcreek link, looking at the others now
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792649
So simple recovery mode
Backup the whole thing
Shrink
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792650
0
 

Author Comment

by:FutureDBA-
ID: 39792661
it has alwasy been on simple receovery mode, i do a nightly backup. and shrink isnt shriking
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792681
You have to backup the log as well as the DB before the log will shrink

BACKUP LOG dbname WITH TRUNCATE_ONLY
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792684
If you have open transactions it won't work:

DBCC OPENTRAN
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792687
Here is another walkthrough
http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

Two stages re involved:
truncate the log (does not reduce file size)
shrink the files (does reduce file size)
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792690
0
 
LVL 16

Expert Comment

by:Carol Chisholm
ID: 39792692
And again the Microsoft source:

http://msdn.microsoft.com/en-us/library/ms365418(v=sql.90).aspx

Log truncation is essential because it frees disk space for reuse, but it does not reduce the size if the physical log file. To reduce its physical size, the log file must be shrunk to remove one or more virtual log files that do not hold any part of the logical log (that is, inactive virtual log files). When a transaction log file is shrunk, enough inactive virtual log files are removed from the end of the log file to reduce the log to approximately the target size. For more information, see Shrinking the Transaction Log.
0
 

Author Comment

by:FutureDBA-
ID: 39792699
DBCC OPENTRAN returned

Transaction information for database 'ERMS_CU3'.

Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (6174532:33243:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
LVL 16

Accepted Solution

by:
Carol Chisholm earned 500 total points
ID: 39793479
0
 

Author Closing Comment

by:FutureDBA-
ID: 39831089
got it resolved.

thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 64
Query - which index being used? 2 61
Get row count of current SQL query 8 59
Upgrading SQL 2005 Express to 2008 R2 Express 31 131
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 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