Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2005, Large Log Files

Posted on 2014-01-19
13
Medium Priority
?
400 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 total points
ID: 39793479
0
 

Author Closing Comment

by:FutureDBA-
ID: 39831089
got it resolved.

thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

885 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