Solved

SQL Server 2005, Large Log Files

Posted on 2014-01-19
13
372 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
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.

 
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

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
Unable to save view in SSMS 21 70
Sql query 107 64
Sql Server group by 10 30
Loops and updating in SQL Query 9 33
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

823 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