Search This Blog

Monday, May 14, 2012

SQL DBA : SQL 2005 - Move C2 Trace file from one Location to another Location

Move C2 Trace file from one Location to another Location :-

Create a Job With the Option of “ActiveX Script” (Select VB Script)

dim TracePath         ‘Declaring Variable for Assigning the Source path
dim FName              ‘Declaring Variable for Assigning the File Name

TracePath="G:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" ‘Source path of Trace File

   Set fso = CreateObject("Scripting.FileSystemObject")  ‘Object Creation for File system 
   Set folder = fso.GetFolder(Left(TracePath,len(TracePath)-1)) ‘Object Creation for Folder Access 
   Set files = folder.Files                                     ‘Object Creation For File Access


 For each fileIdx In files                     ‘Loop For Accessing the Files in the Source Folder
 FName = fileIdx.Name
              if right(FName ,4) = ".trc" then  ‘Checking the file Extension of the Trace file
                 Redim Preserve allFiles(i) 
                 Redim Preserve allTimeStamps(i)
                 allFiles(i)=fileIdx.Name       ‘Storing a Filename in a Array
                 allTimeStamps(i)=fileIdx.DateLastModified ‘Storing the Datemodified value of file
              end if

               'Now sort as per Modified TimeStamp  
 for i =  lbound(allTimeStamps) to ubound(allTimeStamps)
       for j =  lbound(allTimeStamps) to ubound(allTimeStamps)
              if allTimeStamps(i)<allTimeStamps(j) then
              end if

for i =  lbound(allFiles) to ubound(allFiles) -1        ' -1 so that we leave the latest file
       fso.movefile TracePath&allFiles(i) ,"P:\Audit\"  ‘Move the trace to Destination Path

set fso = Nothing     ‘Clearing the Created Objects
set folder = Nothing
Set files = Nothing

Finally you can create a Step for Success and failure status mail for Mail Alert

No comments: