SQL Server 2005, Large Log Files

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.
FutureDBA-Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Carol ChisholmCommented:
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
 
FutureDBA-Author Commented:
i tried that, it doesnt shrink the log file. the fogcreek link, looking at the others now
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Carol ChisholmCommented:
So simple recovery mode
Backup the whole thing
Shrink
0
 
Carol ChisholmCommented:
0
 
FutureDBA-Author Commented:
it has alwasy been on simple receovery mode, i do a nightly backup. and shrink isnt shriking
0
 
Carol ChisholmCommented:
You have to backup the log as well as the DB before the log will shrink

BACKUP LOG dbname WITH TRUNCATE_ONLY
0
 
Carol ChisholmCommented:
If you have open transactions it won't work:

DBCC OPENTRAN
0
 
Carol ChisholmCommented:
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
 
Carol ChisholmCommented:
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
 
FutureDBA-Author Commented:
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
 
FutureDBA-Author Commented:
got it resolved.

thanks
0
All Courses

From novice to tech pro — start learning today.