December 21, 2012 at 9:42 am
I'm encountering a strange problem and was wondering if anyone might have some suggestions of other things I might check.
I've got a SQL 2008 R2 SP4 server that has lots of SSIS packages that are executed by scheduled jobs. They've been running daily for months, if not over a year. Recently 2 packages became corrupted. The jobs that run them fail with the error:
Executed as user: <network account>. The step did not generate any output. The return value was unknown. The process exit code was -1073741819. The step failed.
I am unable to export the packages via SSMS - doing so causes SSMS to crash (the screen goes all white and Windows says SSMS has stopped working and closes the program). If I try to export the package using the command line dtutil, that program crashes. If I open the SQL job that runs the package, open the step that runs that package, and click on the Command Line tab, SSMS crashes. On one of the packages, the schedule name has been changed to a string of random characters.
All that seems to point to a corrupt msdb database (the packages are stored in sql server). However, dbcc checkdb on msdb does not report any errors. There are no error messages in the sql logs or the Windows event logs. chkdsk on the drive where msdb is stored does not return any errors. chkdsk also found no errors on the OS drive or the SQL executable drive.
I restored a backup of the msdb database from 2 weeks ago (the oldest I have) to a new server and the problem showed up there.
The server is a virtual machine and the drives are vdk files. Our VM admin doesn't see anything wrong with the vdks.
Yesterday, the problem started happening with a new SSIS package, so whatever it is appears to be spreading.
At this point, we are going to shut down the VM and convert the drives from vdks to raw LUNs on our SAN. That won't fix existing corruption, but if the cause is disk-related, moving to a new set of disks should stop it from spreading.
No recent SQL patches were applied. We did some standard Windows update patches, but we did that to all of our servers without incident elsewhere.
Does anyone have any other suggestions of steps I might take?
December 21, 2012 at 10:52 am
Are you from the future? SQL 2008 R2 SP4? Just want to level the set before we dig in.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2012 at 10:56 am
Silly question (maybe):
Have you tried the export from the server itself? Another workstation with SSMS installed?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 21, 2012 at 11:04 am
Would like to know the version of the instance but one other beginning question, have you tried selecting the XML out of msdb.dbo.sysssispackages to see if the XML content has been munged??
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2012 at 12:41 pm
Sorry - SQL 2008 R2 SP2.
Jason - yes - tried exporting from SSMS on my workstation, on the server itself. Tried accessing it using BIDS from both locations. Nothing works.
opc.three - Not sure what you mean. sysssispackages doesn't have xml columns. The package is stored as a blob in an image column.
December 21, 2012 at 1:12 pm
shaun.stuart (12/21/2012)
opc.three - Not sure what you mean. sysssispackages doesn't have xml columns. The package is stored as a blob in an image column.
SELECT [Name] AS [SSIS Name],
CAST(CAST(CAST(PackageData AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML)
FROM msdb.dbo.sysssispackages;
If you get a parse error on the CAST to XML then remove that CAST and try to find the package that no longer contains valid XML.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2012 at 1:29 pm
That statement runs without errors.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply