i am coming in after the fact of deploying this plan. i am trying track down a problem with an Optimization Plan that is throwing the error message/number below. usually i can discern the problem but this plan has a few added twists that i have not seen before and so is adding the difficulty for myself of problem solving. they used the Plan wizard to create for the most part and then added/removed databases after initial setup and as well, added components from the toolbox in the GUI. Basic flow: Check Database Integrity Task (takes about 8-9hrs) Reorganize Index Task (takes about 1-3hrs) Shrink database Task (takes about 1hr) It errors out at step 2, and provides the error information below but does not explicitely tell me what database it is doing the index task on. Error Information: Number: -1073548784 Message: Executing the query "ALTER INDEX [PK_ClientPolicyCache] ON [dbo].[ClientPolicyCache] REORGANIZE WITH ( LOB_COMPACTION = ON )" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any way i can find out what database it is hanging up on or a further piece of information about the error?"I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

You got most probably a database related issue, not a SSIS problem.See if any of the replies here are applicable.Arthur My Blog

You got most probably a database related issue, not a SSIS problem.See if any of the replies here are applicable. Arthur My Blog This is sort of correct as a Maintenance Plan is a SSIS package :-)Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com

i am coming in after the fact of deploying this plan. i am trying track down a problem with an Optimization Plan that is throwing the error message/number below. usually i can discern the problem but this plan has a few added twists that i have not seen before and so is adding the difficulty for myself of problem solving. they used the Plan wizard to create for the most part and then added/removed databases after initial setup and as well, added components from the toolbox in the GUI. Basic flow: Check Database Integrity Task (takes about 8-9hrs) Reorganize Index Task (takes about 1-3hrs) Shrink database Task (takes about 1hr) It errors out at step 2, and provides the error information below but does not explicitely tell me what database it is doing the index task on. Error Information: Number: -1073548784 Message: Executing the query "ALTER INDEX [PK_ClientPolicyCache] ON [dbo].[ClientPolicyCache] REORGANIZE WITH ( LOB_COMPACTION = ON )" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Any way i can find out what database it is hanging up on or a further piece of information about the error? "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time. Have a read of the following thread - http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/34c8edde-c74b-4c8f-a3ed-841205d6a6be The outcome of this thread was to lodge a bug with Microsoft. Not sure what the outcome was as this thread has not been marked as answered.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com

I forgot to mention that you should never do this Shrink database Task (takes about 1hr) as it will affect database performance.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: MrWharty.wordpress.com

All of this is being performed after hours anyways. Your right, personally i MAY shrink a database once a month or quarter but not nightly. It is not for me to say as it is the clients strategy, i think ;) They havent put much into effective logging on these optimizations so i have put a log mechanism associated with the plans to see the details of the operations. I figured it was a Database issue but problem is the Error Logs associated with the plans are non-descriptive. I am a programmer by trait but have expanded to db/sys admin responsibilities during the past 5 yrs and so i am used to a stack-trace model error reporting. Unless there is a better way than looking at the history, sql agent logs, or sql server logs; as all 3 of these have absolutely no details , WHERE, and WHEN the error occurred. As well the error number provided means absoluted nothing as there are a handful of error numbers i see for every single error that i have seen occurr, roughly about 30 different unique errors (not related to a specific database/table/view/sproc/etc)."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

... I figured it was a Database issue but problem is the Error Logs associated with the plans are non-descriptive. I am a programmer by trait but have expanded to db/sys admin responsibilities during the past 5 yrs and so i am used to a stack-trace model error reporting. Unless there is a better way than looking at the history, sql agent logs, or sql server logs; as all 3 of these have absolutely no details , WHERE, and WHEN the error occurred. As well the error number provided means absoluted nothing as there are a handful of error numbers i see for every single error that i have seen occurr, roughly about 30 different unique errors (not related to a specific database/table/view/sproc/etc). "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time. Could you share how you fixed this issue please? Regarding the logging, again it is not so much SSIS related, if you want better logging capabilities try implementing logging in your SSIS packages. Arthur My Blog

Cant say its fixed until tomorrow. Otherwise i have the history log setup with step output on the associate job step. I split the "presumed" problem database out into its own optimization pattern (similar to the original) and waiting till it runs at 6PM tonight. pushed the original back an hour for reduction in processing problems. Backend problem is they are using EMC Avamar, SQL Agent jobs, and VEEAM to backup databases across 3 of their servers. Personally, i follow the rule of Anti-Virus software, only use 1 process to do the job instead of 3, as eventually one, or all, will conflict with each other especially if 2 of the 3 use a locking mechanism to do their backups. Cant remember but i dont believe 2005 has the snapshot mechanism like 2008 does. I maybe wrong but i dont remember seeing anything about snapshot's in 2005."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Regarding the logging, again it is not so much SSIS related, if you want better logging capabilities try implementing logging in your SSIS packages. Arthur My Blog If only it was SSIS, i would have put a ScriptComponent in and performed the job with ADO.Net and SQL scripting tasks. they utilized the Maint Plan wizard to create the jobs causing the problem, so alot of the SQL script is probably overly convoluted and cumbersome. I came in on the problem Tuesday and this problem has had a inconsistent history of erroring out over the last 3 months. I am a fireman at the moment, but am trying to turn a reaction request to a preventative request. I am not particularly happy always behind the 8-ball but instead like to jump the 8-ball and solve the problem(s) instead of fixing the symptom(s)."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Well final solution was a VERY VERY VERY Tight scheduling of the jobs. We removed the Backup (Data and Logs) from SQl and left them solely in the "hands" of Avamar. Since they also have file-system backup that is catching the server's HDD, i figured that two backups were good enough."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.