Hi all,

This is going to turn out to be something painfully simple, I'm sure... but
I've looked and looked but can't see the problem. I need fresh eyes to check
it for me, but my fellow DBA is on leave at the moment.

This is a proc I'm writing to purge log backup files used in log shipping
when the drive they're on nears capacity. We usually have enough space, but
if loads of index maintenance or activity takes place, then the logs can use
all available space, causing backups to fail. We keep about 3 days history,
so can afford to delete files that have already been copied to the secondary
log shipping server.

Anyway, the proc is below (with server names, etc changed). I have (as far
as I can see) matching BEGIN and END statements, but QA tells me I have one
END too many;
----------------------------------------------------
USE AdminDB
GO
IF OBJECT_ID('dbo.usp_DBA_LogFileAutoPurger') IS NOT NULL
DROP PROC dbo.usp_DBA_LogFileAutoPurger
GO
CREATE PROC [dbo].[usp_DBA_LogFileAutoPurger]
( @LogBackupDrive char(1) = 'K', -- The letter of the drive that the log
backups are written to
@DriveFreeThreshold int = 5000 -- The free space threshold (MB)
) AS
BEGIN
SET NOCOUNT ON

DECLARE @DriveFreeMB int

----------------------------------------------------------------
-- Check for and create admin/temp tables.
----------------------------------------------------------------
IF OBJECT_ID('TempDB..#DriveSpaceCheck') IS NOT NULL
DROP TABLE #DriveSpaceCheck

CREATE TABLE #DriveSpaceCheck([drive] char(1), [MB_Free] int)

IF OBJECT_ID('TempDB..#DirListing') IS NOT NULL
DROP TABLE #DirListing

CREATE TABLE #DirListing(DirFileName varchar(255), FileDate datetime)

IF OBJECT_ID('dbo.tb_DBA_AutoPurgeEmailBody') IS NULL
CREATE TABLE dbo.tb_DBA_AutoPurgeEmailBody (
[RowID] int IDENTITY(1,1),
[EmailMsg] varchar(1000)
)

----------------------------------------------------------------
-- Get current free space for all drives.
----------------------------------------------------------------
INSERT #DriveSpaceCheck
EXEC master.dbo.xp_fixeddrives

----------------------------------------------------------------
-- Quit if the log backup drive is not found.
----------------------------------------------------------------
IF NOT EXISTS(SELECT 1 FROM #DriveSpaceCheck WHERE [drive] = @LogBackupDrive)
BEGIN
PRINT '*** Specified drive (' + @LogBackupDrive + ':) not found ***'
GOTO TheEnd
END

----------------------------------------------------------------
-- Get free space value for log backup drive.
----------------------------------------------------------------
SELECT @DriveFreeMB = [MB_Free] FROM #DriveSpaceCheck WHERE [drive] =
@LogBackupDrive

----------------------------------------------------------------
-- If the free space is above or equal to the threshold.
----------------------------------------------------------------
IF (@DriveFreeMB >= @DriveFreeThreshold)
BEGIN
PRINT @LogBackupDrive + ': drive on ' + @@SERVERNAME + ' has ' +
CAST(@DriveFreeMB AS varchar) + ' MB free.'
END
ELSE
----------------------------------------------------------------
-- If the free space drops below the threshold.
----------------------------------------------------------------
BEGIN
DECLARE @LastFileCopied nvarchar(255)
, @LastFileDate datetime
, @DelFileName nvarchar(255)
, @Cmd nvarchar(500)
, @DelCmd nvarchar(500)
, @DelCnt int
, @DelFileList nvarchar(2000)
, @Email_Msg nvarchar(1000)
, @Email_Sub nvarchar(200)
, @Email_Recipients nvarchar(500)

----------------------------------------------------------------
-- Get the name of the last copied file from the
-- log shipping monitor server.
----------------------------------------------------------------
SELECT @LastFileCopied = last_copied_filename
FROM OPENROWSET('SQLOLEDB',
'FOO_BAR';'foo';'bar',
' SELECT [last_copied_filename]
FROM msdb.dbo.log_shipping_secondaries
WHERE [secondary_database_name] = ''Foobar'' '
)

----------------------------------------------------------------
-- Format the file name, and get the file creation date.
----------------------------------------------------------------
SELECT @LastFileCopied = SUBSTRING(@LastFileCopied, 15, 12)
SELECT @LastFileDate = CAST(SUBSTRING(@LastFileCopied, 1, 8) +
' ' + SUBSTRING(@LastFileCopied, 9, 2) +
':' + SUBSTRING(@LastFileCopied, 11, 2) AS datetime)

TRUNCATE TABLE dbo.tb_DBA_AutoPurgeEmailBody

INSERT dbo.tb_DBA_AutoPurgeEmailBody ([EmailMsg]) SELECT 'Date: ' +
LEFT(CONVERT(varchar, GETDATE(), 120), 10)
INSERT dbo.tb_DBA_AutoPurgeEmailBody ([EmailMsg]) SELECT 'Time: ' +
RIGHT(RTRIM(CONVERT(varchar, GETDATE(), 120)), 8)
INSERT dbo.tb_DBA_AutoPurgeEmailBody ([EmailMsg]) SELECT
'---------------------------'
INSERT dbo.tb_DBA_AutoPurgeEmailBody ([EmailMsg]) SELECT ' '

----------------------------------------------------------------
-- Get a listing of the files in the log backup directory.
----------------------------------------------------------------
SET @Cmd = N'master.dbo.xp_cmdshell ''dir ' + @LogBackupDrive +
':\Backups\TRN_Logs /B'' '

INSERT #DirListing (DirFileName)
EXEC master.dbo.sp_executesql @Cmd

----------------------------------------------------------------
-- Delete any rows that are not related to log shipping.
----------------------------------------------------------------
DELETE #DirListing WHERE [DirFileName] NOT LIKE 'Prod_tlog_%.TRN'

----------------------------------------------------------------
-- Update the table with each file's date stamp.
----------------------------------------------------------------
UPDATE #DirListing
SET [FileDate] = CAST( SUBSTRING(SUBSTRING([DirFileName], 15, 12), 1, 8) +
' ' + SUBSTRING(SUBSTRING([DirFileName], 15, 12), 9, 2) +
':' + SUBSTRING(SUBSTRING([DirFileName], 15, 12), 11, 2) AS datetime)

----------------------------------------------------------------
-- Check if any of the files have already been copied to the DR
-- server. If not, send an alert email, else delete any
-- files that match the criteria.
----------------------------------------------------------------
IF EXISTS (SELECT 1 FROM #DirListing WHERE [FileDate] <= @LastFileDate)
BEGIN
SET @DelCnt = 0

----------------------------------------------------------------
-- Build email message table
----------------------------------------------------------------
INSERT dbo.tb_DBA_AutoPurgeEmailBody ([EmailMsg])
SELECT 'The ' + @LogBackupDrive + ': drive on ' + CAST(@@SERVERNAME
AS varchar) + ' was near capacity (' + CAST(@DriveFreeMB AS varchar) + 'MB

[quoted text, click to view]


I was getting an "Incorrect syntax near the keyword 'END'." error - which to
me meant that there was a mismatch between a BEGIN and END somewhere. I
copied the code from my previous post, and it also compiled fine this time.
I then went back to the original and did a file comparison (using
UltraEdit32), which showed that there was a BEGIN statement which was on the
same line as a commented section (see below for example):

************************************
IF (something = something)
--------------------------
-- Commented description section
--------------------------
BEGIN
Execute stuff
END
************************************

The BEGIN was on the same line as the line of dashes above it, effectively
commenting it out. But in QA it didn't show this - the BEGIN looked like it
was in the right place, it was blue (not green like the commented text) - so
I'm baffled as to how this happened.

All I did was hit enter again after the commented line, and it compiled
without error. Strange. Thanks anyway! :-)

that sound wierd. Thanks anyways, will remember it when I stumble across such
an issue :)

[quoted text, click to view]