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

x
?
Solved

37 GIG LDF!!! How can i clean this up

Posted on 2004-08-18
12
Medium Priority
?
2,016 Views
Last Modified: 2008-01-09
I have about 60 Databases and there LDF files are HUGE. I need a script i can run to clean up all the LDF files.
0
Comment
Question by:Vartana
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11833128
Are they production databases, or development?


0
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 2000 total points
ID: 11834553
If you have current backups, you can do

sp_msforeachdb 'print ''truncing log for ?''  backup log ? with truncate_only'
sp_msforeachdb 'print ''shrinking ?''  dbcc shrinkdatabase(''?'')'
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11834569
If the LDF file is HUGE, it is probably because either you ahven;t backed up your databse recently, and/or your recovery model is set incorrectly.
If the recovery model is set to simple, the log file will contain only 'active' transactions, once a transaction is committed, it is removed from the log. However, with a FULL recovery model, the transaction log will contain all transactions since the last backup. This could be huge.
Make sure you understand the consequences of changing the recovery model before doing so. THe main point of changing to SIMPLE is that you cannot now recover your database to a poin-in-time. You can only recover to the last backup. If your databse fails during the day, any data input since the last backup will be lost.
If you use recovery model FULL, you need to ensure your backup frequency is high enough to ensure the logs don;t get too big between backups. You probably want to schedule your backups at a more regular basis. The easiest way to do this is through setting up a Maintenance Plan in Enterprise Manager.

If you want to use scripts, the commands you need are as follows -

Your options are, depending on which recovery model you want, to change the model to SIMPLE, or to back up the database :

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
OR
BACKUP DATABASE [mydatabase] TO  DISK = N'C:\backups\mybackupfile' WITH  NOINIT ,  NOUNLOAD ,  NAME = N'my databse backup',  NOSKIP ,  STATS = 10,  NOFORMAT

once the transaction log has been minimized, you can shrink the file itself as follows :

use [mydatabase] DBCC SHRINKFILE (N'file_name')

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Vartana
ID: 11836630
how can i make all my databases Simple Recovery ?
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11836706
sp_msforeachdb 'alter database ? set recovery simple'
0
 
LVL 1

Author Comment

by:Vartana
ID: 11836720
Option 'RECOVERY' cannot be set in database 'TEMPDB'.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11836793
To avoid that:

sp_msforeachdb 'if (''?'' != ''tempdb'') alter database ? set recovery simple'
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11836807
Open Enterprise Manager, expand to the databases, right-click a database and choose Properties. Then click on the Options tab and set the recovery mode manually. Repeat for each database.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11836838
And are you sure you want simple recovery mode?  By choosing simple recovery mode, you will no longer be able to make point in time restores....
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 11836867
A good rule of thumb is to use Simple Recovery only on development systems, not on production systems.

And it's essential to remember SjoerdVerweij's condition in his first post, "IF you have current backups..." otherwise you could lose a lot of data with "backup log ? with truncate_only'"
0
 
LVL 1

Author Comment

by:Vartana
ID: 11836897
Moderator split points as you think is fair Please
0
 
LVL 34

Expert Comment

by:arbert
ID: 11837115
"Moderator split points as you think is fair Please"

That's up to you--moderators don't just browse questions.....
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

783 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