Solved

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

Posted on 2012-03-20
5
1,393 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using this function 4 54
SQL works but want to get the XML node data separately 11 56
Need a mirrored QA test site 2 93
Search Text in Views 2 28
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
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 antispam), the admini…

734 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