Solved

SQL Server 2005, Large Log Files

Posted on 2014-01-19
13
367 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spx for moving values to new table 5 59
SQL help 5 47
Incremental load example 2 43
Problem with SqlConnection 5 115
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

18 Experts available now in Live!

Get 1:1 Help Now