Solved

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

Posted on 2012-03-20
5
1,513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 375 total points
ID: 37742849
0
 

Author Comment

by:crazywolf2010
ID: 37742990
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 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 375 total points
ID: 37743149
insert the backup log line.

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

Accepted Solution

by:
Kyle Abrahams earned 375 total points
ID: 37743201
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 37744194
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard 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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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