SQL job error

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • paullie69@hotmail.com
    #1

    SQL job error

    Hi all

    Inherited environment :

    Windows 2003 SP1 Ent
    SQL 2000 Standard
    16 GB RAM

    BOOT INI Switches : /3GB /PAE

    I understand that SQL 2000 standard can only address 2 GB and
    therefore the /3GB switch will only rob the OS of a GB of RAM without
    giving it to SQL as it can only use 2GB

    We have an active X job invoked from a SQL job ran overnight which
    copies large CSVs (5GB) from one folder to another on the SQL
    server. This runs ok for a while (normally after a reboot) but
    intermittently comes back with "Not enough server storage is available
    to process this command"

    The files can then be copied over manually without problem and the
    rest of the job ran successfully

    Could this error be related to only having 1 GB available to the OS as
    its a file copy ?

    Would it be best to remove both the /3GB AND /PAE switches or just
    the /3GB switch to give the OS at least 2 GB to play with ?

    Thankyou in advance for your replies

    Cheers

    Paul

    Tags: None
    • Steve
      #2
      Re: SQL job error

      On Feb 6, 2:53 am, paulli...@hotma il.com wrote:
      Hi all
      >
      Inherited environment :
      >
      Windows 2003 SP1 Ent
      SQL 2000 Standard
      16 GB RAM
      >
      BOOT INI Switches : /3GB /PAE
      >
      I understand that SQL 2000 standard can only address 2 GB and
      therefore the /3GB switch will only rob the OS of a GB of RAM without
      giving it to SQL as it can only use 2GB
      >
      We have an active X job invoked from a SQL job ran overnight which
      copies large CSVs (5GB) from one folder to another on the SQL
      server. This runs ok for a while (normally after a reboot) but
      intermittently comes back with "Not enough server storage is available
      to process this command"
      >
      The files can then be copied over manually without problem and the
      rest of the job ran successfully
      >
      Could this error be related to only having 1 GB available to the OS as
      its a file copy ?
      >
      Would it be best to remove both the /3GB AND /PAE switches or just
      the /3GB switch to give the OS at least 2 GB to play with ?
      >
      Thankyou in advance for your replies
      >
      Cheers
      >
      Paul
      Can you post the code the job executes?

        Comment

        • paullie69@hotmail.com
          #3
          Re: SQL job error

          On 6 Feb, 16:12, "Steve" <morrisz...@hot mail.comwrote:
          On Feb 6, 2:53 am, paulli...@hotma il.com wrote:
          >
          >
          >
          >
          >
          Hi all
          >
          Inherited environment :
          >
          Windows 2003 SP1 Ent
          SQL 2000 Standard
          16 GB RAM
          >
          BOOT INI Switches : /3GB /PAE
          >
          I understand that SQL 2000 standard can only address 2 GB and
          therefore the /3GB switch will only rob the OS of a GB of RAM without
          giving it to SQL as it can only use 2GB
          >
          We have an active X job invoked from a SQL job ran overnight which
          copies large CSVs (5GB) from one folder to another on the SQL
          server. This runs ok for a while (normally after a reboot) but
          intermittently comes back with "Not enough server storage is available
          to process this command"
          >
          The files can then be copied over manually without problem and the
          rest of the job ran successfully
          >
          Could this error be related to only having 1 GB available to the OS as
          its a file copy ?
          >
          Would it be best to remove both the /3GB AND /PAE switches or just
          the /3GB switch to give the OS at least 2 GB to play with ?
          >
          Thankyou in advance for your replies
          >
          Cheers
          >
          Paul
          >
          Can you post the code the job executes?- Hide quoted text -
          >
          - Show quoted text -

          Here it is :

          '
          *************** *************** *************** *************** *************** *************** *******
          ' copy download files to processing folder and remove timestamp from
          file name
          '
          *************** *************** *************** *************** *************** *************** *******
          Option Explicit

          Function Main()
          Dim oFSO, strDownLoadPath , strProcessingPa th, strFolder, strFiles,
          strFile, intCount, strFlagpos, uDate
          Dim strMinDate, strInFileNameDa te, strOutFileName, intEndPos, intLen
          Dim rsFileNames, strFilename, strFound, stroutFileNamed ate

          ' set global log message

          DTSGlobalVariab les("Log_Step") .Value = "Data Load"
          DTSGlobalVariab les("Log_Obj1") .Value = "Copy files to Processing
          folder"
          DTSGlobalVariab les("Log_Obj2") .Value = "starting "
          DTSGlobalVariab les("Log_DataTe xt").Value = " "
          DTSGlobalVariab les("Log_DataNu mber").Value = 0
          DTSGlobalVariab les("Log_Commen t").Value = "running "

          ' find flag file and get earliest time stamp

          ' get files in download directory

          strDownLoadPath = DTSGlobalVariab les("DownLoadFo lder").Value & "\"
          strProcessingPa th = DTSGlobalVariab les("Processing Folder").Value & "\"

          Set oFSO = CreateObject("s cripting.filesy stemobject")


          ' get date in globals

          strMinDate = DTSGlobalVariab les("MinFlagDat e").Value


          ' get expected files names from globals and check if in download
          folder with timestmp

          Set rsFileNames = CreateObject("A DODB.Recordset" )
          Set rsFileNames = DTSGlobalVariab les("DownLoadFi les").Value

          if rsfilenames.rec ordcount = 0 then

          DTSGlobalVariab les("Log_Obj2") .Value = "data missing "
          DTSGlobalVariab les("Log_DataTe xt").Value = "coping Failed"
          DTSGlobalVariab les("Log_Commen t").Value ="No file names in global
          area "

          main = DTSTaskExecResu lt_Failure
          Set rsFileNames = Nothing
          Set oFSO = Nothing
          exit function
          end if


          rsFileNames.mov efirst

          While Not rsFileNames.EOF

          strFilename = rsFileNames.fie lds("ConfigChar ").Value

          If LCase(Left(strF ilename, 6)) = "static" Then
          strInFileNameDa te = strFilename & ".csv"
          Else
          strInFileNameDa te = strFilename & strMinDate & ".csv"
          End If
          stroutFileNamed ate = strFilename & ".csv"

          ' set surrent file in log message

          DTSGlobalVariab les("Log_Obj2") .Value = strInFileNameDa te

          ' MsgBox strDownLoadPath & strInFileNameDa te & " " &
          strProcessingPa th & stroutFileNamed ate

          oFSO.copyFile strDownLoadPath & strInFileNameDa te,
          strProcessingPa th & stroutFileNamed ate




          rsFileNames.mov enext

          Wend

          DTSGlobalVariab les("Log_Obj2") .Value = " "
          DTSGlobalVariab les("Log_DataTe xt").Value = " "
          DTSGlobalVariab les("Log_Commen t").Value =" "
          Set rsFileNames = Nothing
          Set oFSO = Nothing

          Main = DTSTaskExecResu lt_Success

          End Function
          '------------------------------------------


            Comment

            Previous template Next
            Working...