@_oldest_time is set to a future date

Dec 31, 2010 at 6:13 PM
Edited Dec 31, 2010 at 6:16 PM

I might be missing something, but I believe there is an error in the script that creates the job. I’ve been using it for a couple of months now and this week I noticed that the servers that was no SQL Agent history older than the last 24 hours for the instances that I'd implemented it too.  I found the cause, and the “fix”, and wanted to post it here for the benefit of anyone else that might have the same problem.

The section of the job that dynamically sets the “@_oldest_date” variable which in turn is passed as the “@oldest_date” value when it executes sp_delete_backuphistory, sp_purge_jobhistory and sp_maintplan_delete_log, is pasted below. While the intention, from what I gather, is to set the @_oldest_date variable to a value that is 3 months prior to the date/time of execution, it does not do that on my servers.

 /* DELIVERED section of T-SQL that sets the date */
DECLARE @_oldest_time DATETIME
SET @_oldest_time = DATEADD(m, 3, GETDATE());  -- 3 months

DECLARE @error_message NVARCHAR(256);
SET @error_message = N'Delete history older than ' + CONVERT(NVARCHAR(128), @_oldest_time, 126) + N'...';
RAISERROR(@error_message, 0, 42);

 Instead of a point in the past, the job sets “@_oldest_date” to a date/time that is 3 months in the future. When you pass that future date to these stored procedures they will remove ALL history up to the moment the job was run, leaving you with no history at all. Check out the output below to see what I mean. I executed the code snippet against a SQL Server 2008 RTM sp1 Standard Edition instance.

 -- (The example output was generated on 12/31/2010 at 12:37 PM.)
/*------------------------
DECLARE @_oldest_time DATETIME
SET @_oldest_time = DATEADD(m, 3, GETDATE());  -- 3 months

DECLARE @error_message NVARCHAR(256);
SET @error_message = N'Delete history older than ' + CONVERT(NVARCHAR(128), @_oldest_time, 126) + N'...';
RAISERROR(@error_message, 0, 42);
------------------------*/

Delete history older than 2011-03-31T12:37:02.203...

 Now, this may be intentional and/or I might have missed something in the documentation. If so, I apologize for skimming it and for wasting your time with this post. If it wasn’t intentional or if anyone else his having the same thing happened to them, then I offer a simple “fix”, for lack of a more concise descriptor. Any and all feedback is appreciated.

 FIX: Make the number of months that you are “adding” with DATEADD, negative; like below:

 /* MODIFIED VERSION */
DECLARE @_oldest_time DATETIME
SET @_oldest_time = DATEADD(m, -3, GETDATE());  -- 3 months

DECLARE @error_message NVARCHAR(256);
SET @error_message = N'Delete history older than ' + CONVERT(NVARCHAR(128), @_oldest_time, 126) + N'...';
RAISERROR(@error_message, 0, 42);

Output from above snippet (runtime of code below was 12/31/2010 at 12:57 PM):

 /*------------------------
PRINT 'Current Date/Time: ' + CONVERT(varchar, GETDATE(),120) ;

-- MODIFIED VERSION
DECLARE @_oldest_time DATETIME
SET @_oldest_time = DATEADD(m, -3, GETDATE());  -- 3 months

DECLARE @error_message NVARCHAR(256);
SET @error_message = N'Delete history older than ' + CONVERT(NVARCHAR(128), @_oldest_time, 126) + N'...';
RAISERROR(@error_message, 0, 42);
------------------------*/

Current Date/Time: 2010-12-31 12:57:53
Delete history older than 2010-09-30T12:57:53.480...

Coordinator
Jan 22, 2011 at 4:46 PM
Edited Jan 22, 2011 at 6:28 PM

Quite right - somewhat embarressing!

It is corrected by the release of today.

Thanks again.