Robust Posh Script to Delete Files, Checks for Locked Files First   Leave a comment

UPDATE 2/2/2016: I have made a modification to the scripts so that they will only delete actual files in the specified path, and not folders. It does this by using the statement “if ((Test-Path $_.fullname -pathtype leaf)” to check whether the object being considered is a file.

I am back blogging for the New Year 2016! Happy New Year, everyone!

Before we begin, here are source files for the below scripts:

PowerShell Script
SQL Server Agent Script (incorporating the PowerShell Script)

Here is a nice Posh (PowerShell) script I have written to delete from the specified directory files that are older than the number of days specified. The script is robust in the following ways:

  • It checks if a file that is a candidate to delete is locked (and therefore cannot be deleted). If it finds those files, output is generated to let you know the number of files that could not be deleted and lists the names of up to the first 10 files.

Here is an example of the output (formatted for readability):

The error information returned by PowerShell is: 

Error: A total of 1 File(s) Could Not Be Deleted. 
See the following output: 

Error: The Locked File "MyBackupFile.BKP" could not be deleted.
Note: 0 additional locked files could not be deleted. 

Total # Files Older Than (34) Days Successfully Deleted 
From Directory "B:\Backups\myBackups\": 1458.

  • If all deletions are successful, the output looks similar to the following:
Total # Files Older Than (34) Days Successfully Deleted 
From Directory "B:\Backups\myBackups\": 1458.

  • The script can be used to delete from local directories or network shares.
  • I have designed the script to be run as stand-alone Posh script, or as a SQL Server Agent job, which is scheduled to run on a regular basis. I have successfully tested the SS Agent job on all versions of SQL Server from SQL 2008 and above (up through SQL 2014).
  • Output from the PowerShell script is appended to the SQL Server Agent job’s output history, for your later examination.

Note: For the SQL Server Agent job to work, the Windows service account that the SQL Server Agent runs under must have delete rights in the specified directory.

Here is the stand-alone PowerShell script, and an example of a scripted-out SQL Server Agent job.

The only edits to the scripts you may need to make are as follows:

PowerShell script:

#Set the following to the days to keep value
#and the directory value
$daysToKeep = 34
$directory  = 'B:\Backups\myBackups\'

SQL Agent Job script:

SET @BkupDir = 'B:\Backups\myBackups\' --include trailing backslash

#Set the following to the days to keep value
#and the directory value
$daysToKeep = 34

PowerShell script:


#Set the following to the days to keep value
#and the directory value
$daysToKeep = 34
$directory  = 'B:\Backups\myBackups\'

if (Test-Path $directory)
    {
	$olderThanDt = (Get-Date).AddDays(-$daysToKeep)
	$cntDel = 0
	$cntLocked = 0
	$strMsgFilesRemovedBase = "Total # Files Older Than ({0}) Days Successfully Deleted From Directory `"$directory`"`:" -f $daysToKeep
	$strLockedList = ""
	
	gci $directory | foreach {
        $curFullName = $_.Name
        $curLastWrite = $_.LastWriteTime
        
        if ((Test-Path $_.fullname -pathtype leaf) -and ($curLastWrite -lt $olderThanDt))
        {
        try 
         { [IO.File]::OpenWrite($_.FullName).close();
            if ($curLastWrite -lt $olderThanDt)
                {
                $cntDel += 1
                Remove-Item $_.fullname
                }
         }
        catch 
         {
            $cntLocked += 1
            if ($cntLocked -lt 11)
            {
                $strLockedList += "`nError: The Locked File `"$curFullName`" could not be deleted."}
            }
	     }
	}
	
    if ($cntLocked -gt 0)
    {
        $cntLockedAddl = $cntLocked - 10
		if ($cntLockedAddl -lt 0)
		{
			$cntLockedAddl = 0
		}
        $strLockedList += "`nNote: $cntLockedAddl additional locked files could not be deleted."
    }

	if ($cntDel -gt 0)
	    {write-output "$strMsgFilesRemovedBase $cntDel."}
	else
	    {write-output "$strMsgFilesRemovedBase None Found."}

    if ($cntLocked -gt 0)
    {
        throw "Error: A total of $cntLocked File(s) Could Not Be Deleted. See the following output: $strLockedList `n$strMsgFilesRemovedBase $cntDel."
    }

}
else
    {throw "Error: Specified directory could not be accessed or does not exist."}

SQL Agent Job script:


USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @BkupDir NVARCHAR(1000), @cmd NVARCHAR(MAX), @cmdVar NVARCHAR(MAX)
SET @BkupDir = 'B:\Backups\myBackups\' --include trailing backslash

DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

SET @cmdVar = N'#Set the following to the days to keep value
#and the directory value
$daysToKeep = 34
$directory  = ''' + @BkupDir + N'''

if (Test-Path $directory)
    {
	$olderThanDt = (Get-Date).AddDays(-$daysToKeep)
	$cntDel = 0
	$cntLocked = 0
	$strMsgFilesRemovedBase = "Total # Files Older Than ({0}) Days Successfully Deleted From Directory `"$directory`"`:" -f $daysToKeep
	$strLockedList = ""
	
	gci $directory | foreach {
        $curFullName = $_.Name
        $curLastWrite = $_.LastWriteTime
        
        if ((Test-Path $_.fullname -pathtype leaf) -and ($curLastWrite -lt $olderThanDt))
        {
        try 
         { [IO.File]::OpenWrite($_.FullName).close();
            if ($curLastWrite -lt $olderThanDt)
                {
                $cntDel += 1
                Remove-Item $_.fullname
                }
         }
        catch 
         {
            $cntLocked += 1
            if ($cntLocked -lt 11)
            {
                $strLockedList += "`nError: The Locked File `"$curFullName`" could not be deleted."}
            }
	     }
	}
	
    if ($cntLocked -gt 0)
    {
        $cntLockedAddl = $cntLocked - 10
		if ($cntLockedAddl -lt 0)
		{
			$cntLockedAddl = 0
		}
        $strLockedList += "`nNote: $cntLockedAddl additional locked files could not be deleted."
    }

	if ($cntDel -gt 0)
	    {write-output "$strMsgFilesRemovedBase $cntDel."}
	else
	    {write-output "$strMsgFilesRemovedBase None Found."}

    if ($cntLocked -gt 0)
    {
        throw "Error: A total of $cntLocked File(s) Could Not Be Deleted. See the following output: $strLockedList `n$strMsgFilesRemovedBase $cntDel."
    }

}
else
    {throw "Error: Specified directory could not be accessed or does not exist."}'

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Posh - Clean Up Old Backup Files', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Delete old files]    Script Date: 10/13/2015 8:18:11 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Delete old files', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'PowerShell', 
		@command=@cmdVar, 
		@database_name=N'master', 
		@flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'1', 
		@enabled=1, 
		@freq_type=8, 
		@freq_interval=32, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20151008, 
		@active_end_date=99991231, 
		@active_start_time=233000, 
		@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Enjoy!

Advertisements

Posted January 1, 2016 by Norm Enger in Microsoft SQL Server, Powershell

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: