Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's Blog Category: SQLTom Muck's Blog

News and Views

Upgrades and Backups Pt. 2: Shrinking SQL Server Express Log Files

Saturday, February 26, 2011 8:21:55 AM

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>

Category tags: Macromedia/Adobe, ColdFusion, SQL, SQL Express

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2024 Tom Muck | Dreamweaver Extensions