471,089 Members | 1,802 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

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

Feb 6 '07 #1
2 2032
On Feb 6, 2:53 am, paulli...@hotmail.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?

Feb 6 '07 #2
On 6 Feb, 16:12, "Steve" <morrisz...@hotmail.comwrote:
On Feb 6, 2:53 am, paulli...@hotmail.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, strProcessingPath, strFolder, strFiles,
strFile, intCount, strFlagpos, uDate
Dim strMinDate, strInFileNameDate, strOutFileName, intEndPos, intLen
Dim rsFileNames, strFilename, strFound, stroutFileNamedate

' set global log message

DTSGlobalVariables("Log_Step").Value = "Data Load"
DTSGlobalVariables("Log_Obj1").Value = "Copy files to Processing
folder"
DTSGlobalVariables("Log_Obj2").Value = "starting "
DTSGlobalVariables("Log_DataText").Value = " "
DTSGlobalVariables("Log_DataNumber").Value = 0
DTSGlobalVariables("Log_Comment").Value = "running "

' find flag file and get earliest time stamp

' get files in download directory

strDownLoadPath = DTSGlobalVariables("DownLoadFolder").Value & "\"
strProcessingPath = DTSGlobalVariables("ProcessingFolder").Value & "\"

Set oFSO = CreateObject("scripting.filesystemobject")
' get date in globals

strMinDate = DTSGlobalVariables("MinFlagDate").Value
' get expected files names from globals and check if in download
folder with timestmp

Set rsFileNames = CreateObject("ADODB.Recordset")
Set rsFileNames = DTSGlobalVariables("DownLoadFiles").Value

if rsfilenames.recordcount = 0 then

DTSGlobalVariables("Log_Obj2").Value = "data missing "
DTSGlobalVariables("Log_DataText").Value = "coping Failed"
DTSGlobalVariables("Log_Comment").Value ="No file names in global
area "

main = DTSTaskExecResult_Failure
Set rsFileNames = Nothing
Set oFSO = Nothing
exit function
end if
rsFileNames.movefirst

While Not rsFileNames.EOF

strFilename = rsFileNames.fields("ConfigChar").Value

If LCase(Left(strFilename, 6)) = "static" Then
strInFileNameDate = strFilename & ".csv"
Else
strInFileNameDate = strFilename & strMinDate & ".csv"
End If
stroutFileNamedate = strFilename & ".csv"

' set surrent file in log message

DTSGlobalVariables("Log_Obj2").Value = strInFileNameDate

' MsgBox strDownLoadPath & strInFileNameDate & " " &
strProcessingPath & stroutFileNamedate

oFSO.copyFile strDownLoadPath & strInFileNameDate,
strProcessingPath & stroutFileNamedate


rsFileNames.movenext

Wend

DTSGlobalVariables("Log_Obj2").Value = " "
DTSGlobalVariables("Log_DataText").Value = " "
DTSGlobalVariables("Log_Comment").Value =" "
Set rsFileNames = Nothing
Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function
'------------------------------------------
Feb 7 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Gregory | last post: by
7 posts views Thread by p | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.