Solved

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

Posted on 2004-08-18
12
1,995 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
SQL Syntax 5 33
Webservices in T-SQL 3 30
sql server insert 12 29
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now