Powered By Blogger

Saturday, February 6, 2010

Powershell – How to rename and move files ?

Scenario:  Recently, I came across a situation where in we had a a folder called Job_logs which is used for an output for SQL server agent job logs. All the jobs that are scheduled by SQL agent like SSIS packages, TSQL scripts, etc have their output files written to this Job_logs folder (this is done for better logging).

Now the situation is that when the output file for SQL agent is set to append the log file, the output file which is a .txt file gets bigger in size in long run.

Now when the output file is big lets say more than 5000KB, it downgrades the performance as the output file has to be opened up and then written !

Approach: To overcome above situation, I thought of writing a handy script that will do 2 tasks:

  1. Rename the file that is above 5000KB.
  2. Move the renamed file to another folder called Archive.

  As we are on Windows Server 2008, the first thing came to my mind is using Powershell. It make life easier !

Now the question that come to mind is: “Is 5000 a magical number?” The answer is NO. Our servers are very heavy in terms of CPU (8 cores) and RAM (64GB). So after doing some testing, I came to the conclusion of going with 5000KB files.

Below is the Powershell script that I wrote to achieve what I wanted:

## set the file location where the job log files are
$file = "F:\temp\Job_logs"
## this will get the current date and format it and store in the variable
$ext = get-Date -format MMddyyyyhhmmss
## Loop through all the .txt files in the job_logs Folder
foreach ($file in gci $file -include *.txt -recurse)
{    ## using the length property of the file
     if ($file.Length -gt 5000KB ) 

   { ## will rename the file as file_datetime.txt
    rename-item -path $file -newname ($file.Name + "_$ext.txt")   
       }
};

$file = "F:\temp\Job_logs"
$archive = "F:\temp\archive\"
foreach ($file in gci $file -include *.txt -recurse)
{   
     if ($file.Length -gt 5000KB )
   { ## Move the files to the archive folder
    Move-Item -path $file.FullName -destination $archive 

  }
}

No comments:

Post a Comment

This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.