Tuesday, March 10, 2009

Proactive File Deletion

One of the things I've been trying to accomplish lately is automating more of the repetative "DBA 101" type tasks as much as possible. Among other things, freeing up this time allows me to concentrate your efforts in areas where there a greater value is added from a DBA's input like performance tuning or security auditing. It also greatly reduces the chance of making mistakes, I'm sure we've all had those "execute... whoops!" moments that just make you feel like a fool, or even worse, make you look like a fool in front of your boss. Recently I took a look at our drives & noticed 2 main areas where I thought a scripted solution made sense and was safe (this will vary according to your environment's needs). The areas I decided to focus on were deleting old Red-Gate SQLBackup log files & deleting old backup files. I'm a big fan of Reg-Gate so I don't want this taken as a jab against them in any way. That being said, the Red-Gate GUI places log files in "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\" by default with no retention policy, you can set a retention on these logs, but you have to do it on a case by case basis & frankly I'm too efficient (read: lazy) to do it this way, plus I tend to forget to change the setting. We store all our backups in a specific directory named sqlbu (name changed to protect the innocent) so my backup deletion script is geared to look for that directory on all drives & only delete from that location. The backup delete script also has a few filters that will allow backup files to be kept longer than the days to keep indicated. Specifically it will not delete the file if it has "goldcopy" or "donotdelete" in the name or has "do_not_delete" in the path. I created a SQL Agent job on all our servers that runs 2 powershell scripts that do the actual work, the scripts also return how many files were deleted. The scripts both take a param for the days to keep. SQL Agent Job...
  1. USE [msdb]   
  2. GO   
  3.   
  4. EXEC msdb.dbo.sp_add_operator @name=N'DBA',    
  5.         @enabled=1,    
  6.         @weekday_pager_start_time=90000,    
  7.         @weekday_pager_end_time=180000,    
  8.         @saturday_pager_start_time=90000,    
  9.         @saturday_pager_end_time=180000,    
  10.         @sunday_pager_start_time=90000,    
  11.         @sunday_pager_end_time=180000,    
  12.         @pager_days=0,    
  13.         @email_address=N'dba@yourcomp.com',    
  14.         @category_name=N'[Uncategorized]'  
  15. GO   
  16.   
  17. BEGIN TRANSACTION  
  18. DECLARE @ReturnCode INT  
  19. SELECT @ReturnCode = 0   
  20.   
  21. DECLARE @jobId BINARY(16)   
  22. EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'00 Old File Cleanup',    
  23.         @enabled=1,    
  24.         @notify_level_eventlog=0,    
  25.         @notify_level_email=2,    
  26.         @notify_level_netsend=0,    
  27.         @notify_level_page=0,    
  28.         @delete_level=0,    
  29.         @description=N'The job will delete all *.bak & *.sqb files older than 3 days that do not have “goldcopy” or “donotdelete” in the name & do not have “do_not_delete” in the path.  The job will also delete all RG log files that are older than 7 days in the “C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\” folder.',    
  30.         @category_name=N'[Uncategorized (Local)]',    
  31.         @owner_login_name=N'sa',    
  32.         @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT  
  33. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  34. /****** Object:  Step [Backup Files]    Script Date: 02/25/2009 15:38:25 ******/   
  35. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Files',    
  36.         @step_id=1,    
  37.         @cmdexec_success_code=0,    
  38.         @on_success_action=3,    
  39.         @on_success_step_id=0,    
  40.         @on_fail_action=2,    
  41.         @on_fail_step_id=0,    
  42.         @retry_attempts=0,    
  43.         @retry_interval=0,    
  44.         @os_run_priority=0, @subsystem=N'CmdExec',    
  45.         @command=N'powershell "& C:\WINDOWS\Script\del-oldbackups-single.ps1 3"',    
  46.         @flags=0   
  47. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  48. /****** Object:  Step [RG Logs]    Script Date: 02/25/2009 15:38:25 ******/   
  49. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'RG Logs',    
  50.         @step_id=2,    
  51.         @cmdexec_success_code=0,    
  52.         @on_success_action=1,    
  53.         @on_success_step_id=0,    
  54.         @on_fail_action=2,    
  55.         @on_fail_step_id=0,    
  56.         @retry_attempts=0,    
  57.         @retry_interval=0,    
  58.         @os_run_priority=0, @subsystem=N'CmdExec',    
  59.         @command=N'powershell "& C:\WINDOWS\Script\del-rglogs-single.ps1 7"',    
  60.         @flags=0   
  61. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  62. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1   
  63. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  64. EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sat-Weekly',    
  65.         @enabled=1,    
  66.         @freq_type=8,    
  67.         @freq_interval=64,    
  68.         @freq_subday_type=1,    
  69.         @freq_subday_interval=0,    
  70.         @freq_relative_interval=0,    
  71.         @freq_recurrence_factor=1,    
  72.         @active_start_date=20090225,    
  73.         @active_end_date=99991231,    
  74.         @active_start_time=30000,    
  75.         @active_end_time=235959   
  76. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  77. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'  
  78. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback   
  79. COMMIT TRANSACTION  
  80. GOTO EndSave   
  81. QuitWithRollback:   
  82.     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION  
  83. EndSave:   
  84. GO   
Del-OldBackups-Single.ps1...
  1. $factor = $Args[0]   
  2.   
  3. $disks = Get-WmiObject Win32_Volume -filter "DriveType = 3"  
  4. $cutoff = (Get-Date).AddDays(-$factor)   
  5.   
  6. ForEach ($drv in $disks)   
  7. {   
  8.     $path = $drv.Name + "sqlbu\"  
  9.       
  10.     if ((Test-Path $path) -eq "True")  
  11.     {  
  12.         $count = (Get-ChildItem $path -include *.sqb,*.bak -Exclude *goldcopy*,*donotdelete* -recurse |   
  13.                     ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer -and $_.PSPath -notlike "*do_not_delete*"}).Count  
  14.         if ($count -eq $null) {$count = 0}  
  15.               
  16.         Get-ChildItem $path -include *.sqb,*.bak -Exclude *goldcopy*,*donotdelete* -recurse |  
  17.         ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer -and $_.PSPath -notlike "*do_not_delete*"} |  
  18.         Remove-Item  
  19.         Write-Host "There were" $count "backup files deleted from" $path  
  20.     }   
  21. }  
Del-RGLogs-Single.ps1...
  1. $factor = $Args[0]   
  2.   
  3. $cutoff = (Get-Date).AddDays(-$factor)   
  4.   
  5. $path = "C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\"  
  6.  
  7. if ((Test-Path $path) -eq "True")  
  8. {  
  9.     $count = (Get-ChildItem $path -include *.log -recurse |   
  10.                 ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer}).Count  
  11.     if ($count -eq $null) {$count = 0}  
  12.           
  13.     Get-ChildItem $path -include *.log -recurse |  
  14.     ?{$_.LastWriteTime -lt $cutoff -and !$_.PSIsContainer} |  
  15.     Remove-Item  
  16.     Write-Host "There were" $count "files deleted"  
  17. }  
  18. else {Write-Host "There are no files to be deleted"}