Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2004-08-18
12
1,997 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 500 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

766 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