CODEWITHSUNDEEP
sqlsql-servervbscriptsql-agent-job
ojhawkins
ojhawkins

Reputation: 3278

SQL Sever(2005) Agent not completing VBS script

When SQL Agent runs the below daily script in SQL Server 2005 as part of a job it does not appear to execute the last line set fso = nothing and objects are never deleted from memory causing the Job to fail after a month or so.

Is there any reason why SQL Agent would not appear to execute this last line of code?

dim fso

set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists("E:\Foo.mdb") Then
    fso.DeleteFile "E:\Foo.mdb", True
End If

fso.CopyFile "E:\Foo.temp.mdb", "E:\Foo.mdb", True 

set fso = nothing

Upvotes: 0

Views: 143

Answers (1)

Ben
Ben

Reputation: 35613

Firstly, you should be applying patches monthly when they come out, hence rebooting monthly.

The days when people proudly measured uptime in years are gone due to the unfortunately all-too-necessary patch cycle. If you genuinely need 24/7/365 access (hint: unlikely) you need a high-availability setup with clustering or replication.

Secondly, I don't think your diagnosis is correct. All objects should be released when the VBScript engine object is destroyed, regardless of whether you released them explicitly. So do you think the FSO is being held in memory? How have you confirmed this?

You could in principle use sysinternals Process Explorer check that the associated DLL is unloaded by the SQL server agent process, which will happen usually within a few minutes of the last object being destroyed.

Upvotes: 1

Related Questions