Solved

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

Posted on 2004-08-18
12
1,999 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
monitoring configuration for SQL server DB 32 50
point in time restore in SQL server 26 46
T-SQL: Stored Procedure Syntax 3 34
SQL - Ordering Supervisor Hierarchy 2 15
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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