Cleanup History has an extra output message. Unable to understand how it is appearing. Please help

Question

Post reply

Cleanup History has an extra output message. Unable to understand how it is appearing. Please help

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 16, 2015 at 4:05 pm

#299910

/*
Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015
Running Cleanuphistory Procedure On Server:
NO of records are qualifying for deletion:0
Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1
*** END OF PROCEDURE. ***
*/
USE [MASTER]
go
IF OBJECT_ID('Cleanuphistory_V2', 'P') IS NOT NULL
DROP PROC Cleanuphistory_V2
GO
CREATE PROCEDURE [dbo].[Cleanuphistory_V2]
AS
BEGIN
BEGIN try
SET NOCOUNT ON
DECLARE @oldest_date VARCHAR(20),
@cmd VARCHAR(1000),
@cmd1 VARCHAR(1000),
@cmd2 VARCHAR(1000),
@cmd3 VARCHAR(1000),
@holdNo VARCHAR(10),
@daysOld INT,
@servername sysname,
@DeleteBeforeDate VARCHAR(20),
@oldest VARCHAR(20),
@rows_affected1 int,
@rows_affected2 int,
@rows_affected3 int
-- Holds older than 30 days data to qualify to delete
SET @oldest_date = CONVERT(VARCHAR(10), Dateadd(dd, -30, Getdate()), 110 )
PRINT 'Oldest Day till I want to Purge[30 days of Old Data] : ' + @oldest_date
SET @servername = (Select @@servername)
SET @holdNo = (SELECT Count(*) FROM msdb.dbo.backupset
WHERE backup_finish_date < @oldest_date);
--SET @daysOld = (SELECT DATEDIFF(day,MIN(backup_finish_date),MAX(backup_finish_date)) FROM msdb.dbo.backupset);
--PRINT 'BACKUPSET Hold :' + convert(varchar(10),@daysOld) + 'days of data'
PRINT ''
PRINT 'Running Cleanuphistory Procedure On Server: ' + @servername + CHAR(13);
PRINT 'NO of records are qualifying for deletion:' + @holdNo + Char(13);
SELECT @CMD = 'EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = ''' + @oldest_date + ''''
-- SELECT (@cmd)
EXEC (@cmd)
set @DeleteBeforeDate = (SELECT DATEADD(d,-30, GETDATE()));
SELECT @CMD1 = 'EXEC MSDB.DBO.sysmail_delete_mailitems_sp @sent_before = ''' + @DeleteBeforeDate + ''''
SET @rows_affected1 = (SELECT @@rowcount)
-- SELECT (@CMD1)
EXEC (@CMD1)
PRINT 'Deleted Sysmail MAil Items, Old history: ' + cast(@rows_affected1 as varchar(100));
SELECT @CMD2 = 'EXEC MSDB.DBO.sysmail_delete_log_sp @logged_before = ''' + @DeleteBeforeDate + ''''
SET @rows_affected2 = (SELECT @@rowcount)
-- SELECT (@CMD2)
EXEC (@CMD2)
PRINT 'Deleted Sysmail Log Old history: ' + cast(@rows_affected2 as varchar(100));
SET @oldest = GETDATE() - 30
SELECT @CMD3 = 'EXEC MSDB.DBO.SP_PURGE_JOBHISTORY @oldest_date = ''' +@oldest +''''
SET @rows_affected3 = (SELECT @@rowcount)
-- SELECT (@CMD3)
EXEC (@CMD3)
PRINT 'Deleted SQL JOB Old HISTORY: ' + cast(@rows_affected3 as varchar(100));
PRINT CHAR(13)
PRINT '*** END OF PROCEDURE. ***'
END TRY
BEGIN catch
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(4000);
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorNumber = Error_number(),
@ErrorSeverity = Error_severity(),
@ErrorState = Error_state(),
@ErrorLine = Error_line(),
@ErrorProcedure = Error_procedure();
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, '
+ 'Message: ' + Error_message();
SELECT @ErrorMessage AS [Error_Message];
SELECT @ErrorProcedure AS [Error_Procedure];
PRINT 'Error '
+ CONVERT(VARCHAR(50), Error_number())
+ ', Severity '
+ CONVERT(VARCHAR(5), Error_severity())
+ ', State '
+ CONVERT(VARCHAR(5), Error_state())
+ ', Procedure '
+ Isnull(Error_procedure(), '-') + ', Line '
+ CONVERT(VARCHAR(5), Error_line());
PRINT Error_message();
END catch
SET NOCOUNT OFF
END
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

In a totally unrelated area, why are you using dynamic SQL for every command? It's not needed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

As to your problem, you're taking the value of @@Rowcount before the EXEC for each command.

You've also made things a bit complicated. The following (for example)...

SET @rows_affected1 = (SELECT @@rowcount)

... could be simplified as either of the following...

SET @rows_affected1 = @@rowcount;

SELECT @rows_affected1 = @@rowcount;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

SQL_Hunt

SSC-Dedicated

Points: 33518

April 16, 2015 at 5:16 pm

#1791520

Thnx Jeff..

Thanks.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

You're welcome but you still haven't answered my question. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

I find it easier to write to capture useful details while I write my script...

Thanks.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Ok, then just a suggestion to make your life easier especially since you're not recording the commands that get executed anywhere and you're not taking advantage of the safety of sp_ExecuteSQL...

There's no difference between your method of...

SELECT @CMD = 'EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = ''' + @oldest_date + ''''

EXEC (@cmd)

...and the method of ...

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldest_date

...except that your method is more complex, which makes it more subject to error, and more susceptible to SQL Injection attacks for any procs that do have parameters (this one admittedly doesn't).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)