DTS Package execution fails Error (800403FE)

Question

DTS Package execution fails Error (800403FE)

Andre Beier

SSC Veteran

Points: 270
More actions
April 14, 2004 at 12:02 pm

#161212

I wrote a VBScript to reduce the amount of records in a table with currently
25 Million records (100 MB).
The script worked fine on our development server. However, on the real
server, it failed. The only difference between the two servers is that the
real one is constantly getting data inserted from other processes ( I would
say about 200 records every 5 minutes).
On the real server I get the following error message:
--------------------------------- Error
Message --------------------------------------------------------------------
------------------------------------
Executed as user: SERVER1\SYSTEM. ...art: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482
(800403FE)      Error string: Error Code: 0   Error Source= Microsoft OLE
DB Provider for SQL Server   Error Description: The SQL Server cannot obtain
a LOCK resource at this time. Rerun your statement when there are fewer
active users or ask the system administrator to check the SQL Server lock
and memory configuration.      Error on Line 116         Error source:
Microsoft Data Transformation Services (DTS) Package      Help file:
sqldts80.hlp      Help context: 4500      Error Detail Records:
    Error: -2147220482 (800403FE); Provider Error: 0 (0)      Error
string: Error Code: 0   Error Source= Microsoft OLE DB Provider for SQL
Server   Error Description: The SQL Server cannot obtain a LOCK resource at
this time. Rerun your statement when there are fewer active users or ask the
system administrator to check t... Process Exit Code 1. The step failed.
---------------------------------------------------------------------------
-----------------------------
According to the Error Message I am supposed to change the lock and memory
configuration, but I have no idea how to do that.
Can anyone help me?
I also include my script (see below).
Thanks in advance
Andre
The VBScript looks like this:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
strDeleteWhere = " WHERE "
count = 0
value_sum = 0
maxId = -1
value_avg = 0
SET conDB = CreateObject("ADODB.Connection")
Function Main()
ConnString = "Provider=SQLOLEDB.1;Data Source=(local);Initial
Catalog=MESwebmonitor;trusted_connection=yes;"
conDB.Open ConnString
' Set Timeout to 0 so it won't expire
conDB.CommandTimeout = 0
SET rs = CreateObject("ADODB.Recordset")
sql = "SELECT * FROM performance_t ORDER BY Id, ItemID, LocalDateTime DESC "
rs.Open sql, conDB
currentServer = - 1
currentItem = -1
maxId = -1
WHILE NOT rs.EOF
IF currentServer = rs("id") THEN
IF currentItem = rs("ItemID") THEN
   IF ( maxId <> -1 AND CDate(rs("LocalDateTime")) > DateAdd("n" , -60 ,
CDate(maxDate) ) ) THEN
    count = count + 1
    value_sum = value_sum + rs("value")
    strDeleteWhere = strDeleteWhere & " performanceID = " &
rs("performanceID") & " OR "
   ELSE
    UpdateDB()
                             maxId = rs("performanceID")
    count = 1
    value_sum = rs("value")
                strDeleteWhere = " WHERE "
    maxDate = rs("localDateTime")
   END IF
   rs.MoveNext
ELSE
     ' NEW ITEMID: RESET VALUES BUT DO NOT MOVE RS FORWARD
   currentItem = rs("itemID")
   maxid = -1
          END IF
ELSE
    ' NEW SERVER: RESET VALUES BUT DO NOT MOVE RS FORWARD
currentServer = rs("id")
currentItem = -1
maxid = -1
     END IF
WEND
'Update the db one more time
UpdateDB()
'--clean up conDB
conDB.Close
SET conDB = NOTHING
Main = DTSTaskExecResult_Success
End Function
SUB UpdateDB()
' Only update the DB if we have more than 1 Record
IF count > 1 THEN
value_avg = Round(value_sum / count, 2)
'Delete unnecessary records
         IF strDeleteWhere <> " WHERE " THEN
   sql_del = " DELETE FROM performance_t " & strDeleteWhere & "
performanceID = 0 "
   SET rs_Delete = conDB.Execute(sql_del)
   END IF
'Update the record with the MAXID
sql_up = " UPDATE performance_t SET Value = " & value_avg & " WHERE
performanceID = " & maxID
SET rs_Update = conDB.Execute(sql_up)
END IF
END SUB

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Peter P SSCrazy Points: 2777 More actions · Answer 1

Hi Andre, Try Editing the procs that are performing the insertions, updates, deletes on your production server to include NOLOCK in the select statements, e.g.,

Insert Into TableX(a,b,c,d)

Select a,b,c,d From TableY With (NOLOCK)

NoLock - Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

kokyan SSCommitted Points: 1694 More actions · Answer 2

Andre, I think you should listen to the SQL Server's advice to run the program when the server is not busy with updates. Try to reschedule the DTS to run at the time whenever they are not much transactions from outside.

Considered to move the delete statements to a store procedure and use SQL Task to execute the store procedure. The reason of using the store procedure is to minimize the time for deleting the records and release the lock earlier.

Regards,
kokyan

Andre Beier SSC Veteran Points: 270 More actions · Answer 3

The problem is that the server is always "busy" receiving data (24/7)

I tried the Select... With (NOLOCK) Statement.

The job was running a little bit longer this time but failed again.

How can I move the DELETE Statement to a Stored Procedure?

Another thought: Is there a way to increase the memory size for locks manually?

Thanks in advance

Andre

Peter P SSCrazy Points: 2777 More actions · Answer 4

locks is an advanced option.

Use the sp_configure system stored procedure to change

the setting, you can change locks only when show advanced

options is set to 1. The setting takes effect after

stopping and restarting the server.

to show advanced options:

USE masterEXEC sp_configure 'show advanced option', '1'--Here is the message:Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE command to install.

per Microsoft:
 
Allowing SQL Server to use locks dynamically is the 
recommended configuration. However, you can set 
locks and override SQL Server's ability to allocate 
lock resources dynamically. Increase this value if 
SQL Server displays a message that you have exceeded 
the number of available locks. Because each lock 
consumes memory (96 bytes per lock), increasing this 
value can require increasing the amount of memory 
dedicated to the server.
RECONFIGUREEXEC sp_configure 'locks', 5000 to 2147483647

Rao.V Hall of Fame Points: 3452 More actions · Answer 5

hi....

I'm facing the same type of error

Error string: Error Code: 0 Error Source= Microsoft OLE DB Provider for SQL Server Error Description: Timeout expired Error on Line 38 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= Microsoft OLE DB Provider for SQL Server Error Description: Timeout expired Error on Line 38 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error: -2147217871 (80040E31); Provider Error: 0 (0) . The step failed.

this was taken from job history and its running a DTS package. Owner of the job is server admin.

Any help how i can fix this.. or what is the issue ... is the issue with DTS package or any of the services or ...?