Up until last night, everything with our production SQL Server was running just fine, but a few jobs failed and now I have a problem I can't figure out....
Actually, there were three things that went South last night.... don't know if any of them relate to my problem, but I'll just state them for the record.
1) Database Backup job (created using the Maintenance Plan wizard) failed - event viewer gives the error:
"18210 :
BackupMedium::ReportIoError: write failure on backup device '<BackupFileName>'. Operating system error 2(The system cannot find the file specified.)."
The above job backs up all User Databases and the above happened about halfway through the list of DB's, so each following DB got the same error in the event viewer.
2) 5 Scheduled Jobs that call DTS packages failed. The times were 3, 4, 4:15, 5, and 5:30am this morning. The first four failed because I don't think it liked me putting a "-" in the name of the DTS Package as I got the error:
"Executed as user: SQLPRO001\SYSTEM. ...Tables The parameter is incorrect. DTSRun: Invalid command options ...<the rest cut for brevity>...."
The last job failed because of the third thing....
3) The TempDB log file filled up completely. This happened at a couple minutes after 5am, and the event viewer had:
"Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."
It was the full tempdb log file that first brought this whole thing to my attention as when I tried to refresh the Jobs screen in EM, it gave me that error message. I did the back it suggested by running "BACKUP LOG tempdb WITH TRUNCATE_ONLY" in QA. Then when I refreshed the Job screen, I saw all the red X's. I was able to, by hand, backup all the databases, so I am not sure what the deal is there. Then I saw that four of the failed jobs all had the "-" in their name, where the fifth failed due to the tempdb issue. I also saw that other jobs had run, prior to the tempdb issue, that did not contain the "-" so that's why I am thinking it is an issue there.
So I went to go rename (re-save with a new name actually) the packages but I now cannot open any DTS package. When I double click a package, there is a long pause and then:
Package Error
Error Source : Microsoft OLE DB Provider for SQL Server
Error Source : [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
When I click the "OK" button, then I get a second error message:
DTS Designer Error
The selected package cannot be opened. The DTS Designer has been closed.
I thought maybe they all had been corrupted somehow, and was glad I had previously saved them off to disk as .dts files. So, I do an "Open Package...", the thing comes right up, but when I go to save the package on the server, I get the first error message above. I check the connection information I am giving -- Server set to "(local)" and using "Windows Authentication".... first error above. I try switching to "SQL Server Authentication" with it still pointed to "(local)" and give it an admin account info.... first error above. Switching the server from "(local)" to the name of the machine.... now it works and saves the DTS to the server. However, if I close the designer window, and try to open up that same package right away, I get the two error messages above.
I opened the .dts file as described above, reset all the DB Connections to use the server name instead of "(local)", save the package on the server without any "-" or spaces, updated the Job with the new DTS names, and ran it. This time it didn't error immediately, but when it got to the step that connects to the SQL Server it failed (about 19 seconds into it) giving the error:
"Executed as user: SQLPRO001\SYSTEM. DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed."
I know DTS packages are run on the machine that is opening/calling them, but I am remoted in to the SQL Server, doing nothing different than I did yesterday, the day before, or anytime in the past. I have tried stopping and restarting SQL Server, but still having the issues. I have not tried rebooting the machine, but as this is our Production SQL Server, I was hoping to avoid the dozen or so minute down-time. I can if I have to, but is there something else I can do to fix this situation? Does anyone know what is the problem? How to fix it? What may be the possible cause of it? Anything? I really need to get this resolved as quickly as possible.
Thanks for any help you can offer.
-- Andrew
My new philosophy!
Life's journey is not to arrive at the grave safely in a well preserved body, but rather to skid in sideways, totally worn out, shouting "...holy ***...what a ride!"