May 10, 2007 at 9:46 am
I have an odd problem I have been assigned to debug and am having no luck finding anything online that is even close. Maybe someone else has had this issue. We have a windows 2000 box on sp 1 with Windows 2000 sp 4.
One of our developers discovered that every 3 weeks or so his dts packages are completely empty, no prior versions or anything. The jobs run successfully in one second(nothing to fail) instead of the normal 2 - 3 minute run time. Rebooting the system brings the packages back. Extremely odd!! Does anyone have any clues on where I can go to debug this? Nothing unusual shows up in the sql error logs or the server event logs
This is the senior developer's notes "The issue was that all DTS packages on the server got “blanked out.” Meaning, if you opened up Enterprise Manager on the server and then went to edit any DTS package… the DTS package designer was empty.
The scheduled packages were still running, but they would report success for every run, and the run time was only 1 second. (Of course… it only takes 1 second to run a blank DTS package… and there is no failure if nothing is run!).
The cause is unknown. The ‘fix’ is a restart. The frequency is about once every couple of weeks. The symptoms are, nothing transferring (0 campaigns) and that the DTS packages are reporting a 1 second time instead of the average 2-3 minutes to run."
Any and all help is greatly appreciated
May 10, 2007 at 10:05 am
I have not seen this but where are the packages stored and do the packages get altered regularly?
May 10, 2007 at 11:59 am
The Packages are stored on the server and have not been altered for a long time.
May 10, 2007 at 12:40 pm
Packages are stored within the server in the msdb database along with jobs and backup history. Have any other oddities occurred besides this with regards to jobs? Do all jobs have their text? This is just really odd.
May 10, 2007 at 1:54 pm
The jobs are all there, and no other oddities. Additionally if the DTS package shows up blank on the EM on the server, the jobs run for 1 second and report success even if the dts is empty, but he can still view the dts from his remote connection on his pc via EM for 2000. so this is one of the freakiest things we have seen. Once the server is rebooted the contents of the packages are visable both ways and the jobs then run for the 2-3 minutes normal run time.
May 10, 2007 at 2:27 pm
Lowry,
Are the packages stored in Meta Data Services?
Greg
Greg
May 10, 2007 at 2:33 pm
Greg
They are stored under local packages, we don't have anything stored in Meta Data Services.
November 14, 2007 at 6:00 pm
Did you get a solution to this problem? I've got a customer with a similar problem.
November 14, 2007 at 6:08 pm
Nope, never found a solution.
November 15, 2007 at 5:44 pm
I have seen this problem before when the msdb is out of space. If you add free space to the msdb it may solve your problem.
February 10, 2009 at 12:29 am
I had the same issue. It is indeed a space issue however the issue may be space on the system drive that SQL Server is installed on or where the BIN directory for the installation resides.
The issue is really with Enterprise Manager and not anything wrong with the packages themselves or msdb. If you connect from another SQL EM (your machine perhaps) you will recognize the package data is visible.
In our case the server had 284K of available space on the C drive. After returning free space to approx 1 GB, closing and reopening EM, the package data was visible from EM on the server.
Jay DBA
December 20, 2009 at 3:57 pm
Awesome thanks, I was having the problem too & just noticed 'msdb' properties were not set to 'auto shrink' & grow was set to 1Mb, so I turned on autoshrink & set to grow by 10% & shrank msdb & I can now open my DTS packages. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply