Archive for the ‘Powershell’ Category

Update to my “Robust Posh Script to Delete Files” Script   Leave a comment

I have made a modification to my “Robust Posh Script to Delete Files” 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. HERE is the link to the updated post and scripts.

Posted February 2, 2016 by Norm Enger in Microsoft SQL Server, Powershell

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!

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

A PowerShell Script to Help You in Cleaning Up Old or Unused Files in Your SQL Data File Directories   Leave a comment

Here is a PowerShell script to list non-locked files in SQL Data directories as possible candidates to delete, in order to free up space. This can be useful for identifying old files that, for whatever reason, were not deleted and may be wasting disk space.

Note: before deleting any file, make sure it is not needed. For example, if a database is offline, its data files will be closed (not locked) and thus show up on this report, but you may not want to delete the files in case someone should need to bring the database back online again.

There are two modifiable sections in the script that allow you to prevent files from being evaluated based on either the file extension or the file name.

The script works by simply looping through files in the directory that you pass in as a parameter, and attempts to open the first line of each file. If a file is locked (as in a live SQL data or transaction log file), the attempt to open it and read the first line will fail, and the file will not be reported on. If the attempt to open and read the first line is successful, then the file will be reported on as a candidate for deletion.

Being the paranoid, DBA-type, I have written the script so that it will not perform the actual deletion of any files. Again I recommend that a human being look at each listed file and make the final decision of whether to delete.

I like to run the script from a PowerShell 2.0 ISE window. The example execution in the script assumes you save this script to your local system as “C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1.” Please follow the directions within the comments of the script for a sample execution.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

param([string]$theDir="")

#Norm Enger - 4/30/2011 - PowerShell script to list non-locked files in SQL Data directories
#as possible candidates to delete, in order to free up space. Note: before deleting any
#file, make sure it is not needed. For example, if a database is offline, its data file
#will be closed (not locked) and thus show up on this report, but you would not want to delete
#the file in case someone should need to bring the database back online again.

#To run, open a Powershell window and run a command like the following (starting with
#the amperstand "&" character)...
#& "C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1" -theDir "C:\mySQLDirectory\Data"

#Output will be similar to the following:
#C:\mySQLDirectory\Data\New Text Document.txt (10/8/2010 4:46:33 PM)

#Array of extensions to exclude from report (change as needed)...
$arrExcludeExtensions = ".preg",".old",".cer"

#Array of file names to exclude from report (change as needed)...
$arrExcludeFileNames = "distmdl.mdf","distmdl.ldf", `
	"mssqlsystemresource.mdf","mssqlsystemresource.ldf"

#Sets the folder location
Set-Location $theDir

foreach($file in get-childitem | Sort-Object $_.Name `
	| Where-Object {$arrExcludeExtensions -notcontains $_.Extension `
	-and $arrExcludeFileNames -notcontains $_.Name})
{
#Get-Content returns error if the file is in use/locked
#We limit lines to try to read at 1 using TotalCount...
$content = Get-Content $file -TotalCount 1 -ErrorAction silentlycontinue
	if($?){
		$file.FullName.ToString() + `
		" (" + $file.LastWriteTime.ToString() + ")"
	}
	else{} #write out nothing
}

Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell

Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell