Solved

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

Posted on 2004-08-18
12
1,994 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
email the result out from a T-SQL queries 29 63
Query Peformance + mulitple query plans 9 48
datetime in sql 6 28
SQL Server - Slabs 9 38
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 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

16 Experts available now in Live!

Get 1:1 Help Now