Solved

T-SQL Script to auto shrink transaction log files for all databases on server

Posted on 2012-03-20
5
1,225 Views
Last Modified: 2012-04-10
Hi,
My database transaction log files grow a lot suddenly. I am using simple recovery model.

USE mydb
GO
BACKUP LOG mydb WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(mydb_log,8)
GO

Code as above does the trick but I have more than 25 databases on server. I wish to have a SQL server agent job doing the cleaning work each night.

Have you come across such script?

Thanks
0
Comment
Question by:crazywolf2010
  • 3
5 Comments
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 375 total points
Comment Utility
0
 

Author Comment

by:crazywolf2010
Comment Utility
Hi,
Thanks for the script and it's brilliant.

Unfortunately my transaction logs are not shrinking even after running this script and I suspect I will need truncate log stmts to clean the tail of a log.
Have you come across such script which will truncate log & then shrink log files at all databases?

Thanks
0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 375 total points
Comment Utility
insert the backup log line.

ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;
BACKUP LOG ' + quotename(mf.[name],'''') + ' WITH TRUNCATE_ONLY;
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 375 total points
Comment Utility
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
If your databases are in simple recovery model, then you do not need to explicitly truncate the log -- in fact, you should get an error when you try to, as log backups are not allowed on dbs in simple mode.

Check to see if/why SQL says it cannot adjust the log file:

SELECT
    name, log_reuse_wait_desc, --<< see if this column has a reason
    user_access_desc, state_desc,
FROM sys.databases
WHERE
    name NOT IN ('distribution', 'master', 'model', 'msdb', tempdb') AND
    name NOT LIKE 'reportserver%'
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

772 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