June 25, 2009 at 1:18 am
Hi all,
We are facing problem to delete the log file, those created using other packages.
The problem is:
We have more than 10 SSIS packages. And with this Package we have created Jobs in SQL Server, and all the jobs are scheduled in SQL Server. The scheduled are “Occurs every day every 1 minute(s) between 12:00:00 AM and 11:59:59 PM.”
We have log file (for each job separate file) for all the jobs, each and every minutes the jobs are inserting/updating the log file with the current status.
We have another one job for deleting the log files (files created by above jobs, File/Job name is: Log deletion). In this package, we are trying to delete the directory contained files using the File System Task. For the file system task we given parameter as the above jobs (All the 10 jobs log file path.) log file locations.
Real Problem is:
When we are trying to delete the file using the log deletion package or job, that time all the files in the log folder is used for other jobs). Here we are getting the Sharing violation to delete the file due do that Log files are not getting delete.
I tried the following ways:
Way 1:
In Logdeletion packages I included the following steps:
Step 1: First I disabled all the other jobs (I updated the sysjobs table from msdb database).
Step 2: Running the LogDeletion Package
Step 3: ReEnable all the other jobs
From the above steps:
1.After I disable the job also jobs are running. While running the LogDeletion package time I am getting the same sharing violation problem.
2.Once I enable job most of the time the jobs are not running, once I restart the SQL Agent then only the jobs /scheduled are running. After enable the job whether the jobs will run for next schedule or not.
Way 2:
I written 2 SP it contain the following Step 1, Sep 3
Step 1:. Disable all the jobs.
. If any jobs are running I need to stop the job (using the sp_stop_job from msdb database)
Step 2: I need to run the Log Delete package.
Step 3: Re-Enable the all the job (update sysjobs table)
My Query/doubts are:
Query 1: While I am trying to stop the job, in the same time if the job is running that time what it will happen. What are works it done/completed, that will ROLLBACK or COMMIT. (For example if the jobs is trying to update and insert 1000 record, When I trying to stop the package at that time it already updated and inserted 500 records, If I stopped the job what will happen, what the updated records are will ROLLBACK or Commit.
Query 2: I used for finding job status using the following query “OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes', 'set fmtonly off exec msdb.dbo.sp_help_job')”. For running the above query we need to enable the some “AdHoc Query Settings”. But my client is not accepting to enable the “AdHoc Query Settings” for SQL Server.
Query 3: Last I tried to get the running status for the job using the following system SP “sp_get_composite_job_info”. I getting the error/warning message while executing SP” Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 68 An INSERT EXEC statement cannot be nested.“. How I can get it clear the above error message.
File Location:1.All the jobs are located in the single folder and log file path is same for all packages. (Expect the log deletion packages.)
Could any help me out on this? Any help is appreciated.
Thanks in advance.
Regards
Sundaraguru S
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply