Solved

SQL Server 2005, Large Log Files

Posted on 2014-01-19
13
369 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause in stored procedure 8 50
Move SQL 2005 Express to Server 2012R2 19 104
Query 14 54
Help with simplifying SQL 6 49
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

14 Experts available now in Live!

Get 1:1 Help Now