Tom Muck's Blog Category: SQL ExpressTom Muck's Blog
News and Views
2 posts
Showing 1
| Next
(page 1 of 2)
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
Posted by Tom Muck
|
(0) |
Permalink
|
Trackbacks (0)
|
Digg This
[フレーム]
2 posts
Showing 1
| Next
(page 1 of 2)
Before posting comments or trackbacks, please read the posting policy.