Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-20
5
Medium Priority
?
1,709 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 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 1500 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 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 1500 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 41

Accepted Solution

by:
Kyle Abrahams earned 1500 total points
ID: 37743201
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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