[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2019
  • Last Modified:

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

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
Vartana
Asked:
Vartana
  • 3
  • 3
  • 3
  • +2
1 Solution
 
jdlambert1Commented:
Are they production databases, or development?


0
 
SjoerdVerweijCommented:
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
 
BillAn1Commented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
VartanaAuthor Commented:
how can i make all my databases Simple Recovery ?
0
 
SjoerdVerweijCommented:
sp_msforeachdb 'alter database ? set recovery simple'
0
 
VartanaAuthor Commented:
Option 'RECOVERY' cannot be set in database 'TEMPDB'.
0
 
SjoerdVerweijCommented:
To avoid that:

sp_msforeachdb 'if (''?'' != ''tempdb'') alter database ? set recovery simple'
0
 
jdlambert1Commented:
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
 
arbertCommented:
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
 
jdlambert1Commented:
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
 
VartanaAuthor Commented:
Moderator split points as you think is fair Please
0
 
arbertCommented:
"Moderator split points as you think is fair Please"

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now