Part 1 described how to create a bare bones SQL Server automated backup plan for a SQL Express database using a ColdFusion scheduled task. I also wanted to truncate and shrink old log files on a schedule, so came up with a stored procedure to do it, cobbled together from various snippets. The new stored procedure takes in a database name as parameter, then looks up the log file name. If it's found (it should be if the db name is correct) it truncates and shrinks the file. Here's the script:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_TruncateLogs] 
       @databaseName nvarchar(1024)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(1000)
 DECLARE @logfilename NVARCHAR(1000)

 SELECT @logfilename = b.name
 FROM sys.sysdatabases a 
  INNER JOIN sys.sysaltfiles b
   ON a.dbid = b.dbid
 WHERE fileid = 2
  AND a.name = @databaseName

 IF @@rowcount = 1 BEGIN
 
  SET @sql = '
  USE ' + @databaseName + '
  BACKUP LOG ' + @databaseName + '
  with truncate_only
  dbcc shrinkfile (' + @logfilename + ', 1)'

  EXECUTE sp_executesql @sql
 END

END

Next, just add it to the loop in the original ColdFusion scheduled task script:

<cfstoredproc datasource="master" procedure="dbo.sp_TruncateLogs">
      <cfprocparam cfsqltype="cf_sql_varchar" value="#ListGetAt(variables.databases, variables.i)#" />
</cfstoredproc>