Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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

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
crazywolf2010
Asked:
crazywolf2010
  • 3
4 Solutions
 
crazywolf2010Author Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
insert the backup log line.

ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;
BACKUP LOG ' + quotename(mf.[name],'''') + ' WITH TRUNCATE_ONLY;
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
0
 
Scott PletcherSenior DBACommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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