When SQL Server 2005 first was announced to the world, we all raised our collective glasses and cheered the advent of a new paradigm (see my book review on Scalability Expert's SQL 2005 "Changing the Paradigm"), of an integrated end-to-end business intelligence and database platform.In addition, there were also some unfounded fears that the SQL Server 2005 OS, as it was called, with all its new self-maintaining, self-healing features, along with all the bells and whistles, that the DBA as we know it would become obsolete, meaning our livelihoods.
Then as our fears subsided, we learned that not only would SQL 2005 keep us employed for a long time to come, that our responsibilities would increase, our skills expanded and our knowledge enriched, due in part we were told, that this new system would relieve us of our simple day-to-day administrative duties. Based on the premise of either dynamically managed resources, or set and forget, such as our maintenance backup plans.
In SQL 2000, we came to know, love and even depend on the SQL Database Maintenance Wizard, where with a click of a few buttons, we could quickly setup and schedule our common SQL maintenance tasks such as backups, integrity checks, update statistics, and indexing operations.Much of the simple stuff is still there, and they even added the ability to execute another SQL Agent job, and execute a T-SQL Task (more on this later), all within the maintenance plan itself.Definitely a lot more you can do as you can see from the toolbox menu list of Maintenance Plan Tasks.See more about the new SQL 2005 Maintenance Wizard in the article with the same name.
In order ensure a smooth transition to SQL 2005, I thought that at least the backup plan migration would be a piece of cake.I would let the wizard continue to work its wondrous ways.However, to my amazement, and the consternation of the DBA community, when perusing through the wizard several times, I couldn't find anywhere to indicate the retention period of a backup, to maintain and keep disk storage issues under control.
Fast forward to the release of Service Pack 2 (see SQL Server 2005 Service Pack 2 is Out the announcement on SSC.com). We learn that the DBA wish list has been fulfilled, and that the wizard now will have the cleanup task once again, a "huge omission before this." This was a great win for the DBA (maybe we can form a lobby and become a powerful voting bloc - Steve Jones for President! 😉
Sure there were several new "features" added along with
these "fixes", and that is a whole other debate that I will leave to others in the community to blog about.I happen to be on the side of those who believe that bug fixes and new features should absolutely come in separate packages.Enhancements and new features in a Feature Pack; critical bugs and fixes in a Service Pack.Are you listening Redmond? 😉
So, we would just push up our service pack deployment schedule ahead of time, and apply SQL2K5 SP2 as soon as possible, and that will solve our maintenance malady, right?Not so fast!It's a good thing that many of us cautious folk like to wait a while before applying service packs across our infrastructure.Just when we thought it was safe to schedule our backups, faster than we could click 'Next', MS quickly re-releases its SP2, updated to fix the maintenance plan issue, and informs us
via kb article (933508) that 'cleanup tasks run at different intervals than intended'. This of course only applied to those who downloaded and applied SP2 before March 05,
2007. Great!Just what we needed right before the changeover to DST in the US, and the many of us who were not even certain if and when our SQL jobs would run.(Thank goodness that went off without a hitch.)Please note that according to the aforementioned kb article, this issue only affected those who "use SQL Server 2005 maintenance plans or Integration Services packages."Whew, that's a relief!
To make things even more complicated and stressful, rather than call a spade a spade, or rather the newer SP2, Service Pack 2a, as suggested by Steve Jones in his March 7, 2007 column, we get 'Service Pack for a Service Pack', as one SQL MVP and author, Brian Moran wrote in his column on SQLMag.com.As Brian notes MS announced indeed there was an "issue" with SP2, and deftly adds, "An issue by any other name is just a bug" Why they just re-released it with no new build number to distinguish it from its short-lived predecessor, I'm not sure. We all know about the precedent set by the release of SQL 2000 SP3a and that worked out pretty well.
Where is this all leading to?Ok, there was certainly enough buzz and
debate generated in the press about this faux-paus. I also started writing this before SP2 was out. In adding my two-cents, I am sharing my workaround for those of you who are a bit wary in applying SP2, or are just waiting for SP3, to manage your backup files with my homegrown cleanup task, which will check if the file and/or subdirectory exist, and delete old backups at your desired interval. The below code is used to cleanup ALL old user database backups on a particular storage location/drive.I'm sure you can improve upon it to your liking, but this should get you started.
To set it up, simple create your maintenance backup plan using the wizard. Then, once created, go back, right-click the plan under Maintenance Plans in SSM, and select 'Modify'. Here's the workflow. Add a new 'Execute T-SQL Statement Task' and append it to the 'Backup Database Task' as the next step to execute 'on success' and add the following code and save:
----------------------------------------------------------
--Cleanup Task pre-re-released Service Pack 2
-- created by Robert Pearl
declare @dt datetime
declare @deldaysold int
declare @dbname sysname
declare @mpath varchar(255)
declare @dir varchar(255)
set @dir='Your Directory'
set @deldaysold=4
DECLARE GetDBName CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
select name from sysdatabases
where name not in ('master','model','msdb','tempdb')
OPEN GetDBName
FETCH NEXT FROM GetDBName INTO @dbname
WHILE (@@FETCH_STATUS != -1)
BEGIN
--select @dbname
--Create temporary table
create table #fileexists (
fileexists smallint,
direxist smallint,
parentdir smallint)
set @mpath='Backup File Path' + @dir + '\' + @dbname
--select @mpath
-- Insert into the temporary table
Insert into #fileexists exec master..xp_fileexist @mpath
--Queries the temporary table to see if the file exists
If exists (select direxist from #fileexists FE
where FE.direxist = 1)
Begin
select @dt=getdate()- @deldaysold-- 1 is the files with 1 day old
EXECUTE master.dbo.xp_delete_file 0,@mpath,N'BAK',@dt
--Print @dbname + 'Subdirectory EXISTS HOORAY!'
End
/*Else
Begin
Print 'Subdirectory Does Not Exists'
End*/ -- Clean up TempDB
DROP TABLE #fileexists
FETCH NEXT FROM GetDBName INTO @dbname
end
CLOSE GetDBName
DEALLOCATE GetDBName
--END CODE
Written by: Robert Pearl, President
Pearl Knowledge Solutions, Inc.
Copyright 2007 - All Rights Reserved.
Note: Not to be reprinted or published without express permission of the author.
When SQL is the
