Stored Procedure to shrink log files | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stored Procedure to shrink log files

I am trying to create a store procedure to truncate and shrink the transaction log of all our databases (10) I was trying to use the script:
Create procedure dbo.sp_truncate_resize_log
as
backup log (database name) with truncate_only
DBCC Shrinkfile (database name_log,2) —Repeating this for each database— But I get the message saying could not locate file (database_name_log) in sysfiles So then I tried to include the line Use (database_name) but then get a message saying can not include “Use” in a procedure What I want to do is to set up a job to run nightly to truncate and shrink the log file. We use simple recovery so I am not concerned with the info in the log file. What is the best way to accomplish what I need? Thank you

May be you can use : USE [databasename]
GO
backup log ‘databasename’ with truncate_only
USE [databasename]
GO
DBCC SHRINKFILE (N’databasename_log’ , 5)
GO
Thanks
I would say why you are trying to stress the SQL server by shrinking the log & data on a regular basis. First of all you need to ascertain the log space usage by considering all the process & scheduled jobs, then set the value which is required to accomodate the above processess that will save your time to worry about log space usage and further if there is no issue in free disk space issues then need not worry. SIMPLE recovery model takes care of log space by truncating and when you have AUTOGROW enabled you are not helping the system. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Try this if it works.. I have not checked if it is working…
sp_msforeachdb ‘use ? select db_name() backup log ? with truncate_only’
Satya is right that shrinking tran log nightly is bad idea. For more detailed explanation read hoo-t’s post in the next thread:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894
]]>