| Author |
Topic |
|
dbist
Yak Posting Veteran
52 Posts |
 Posted - 2008-03-25 : 14:51:23
|
| I deleted a database referenced in a maint. task and then I tried to delete the maint. task. I am getting an error "Exception has been thrown by the target of an invocation" and also it says Named Pipes Provider:, error 40: Could not open a connection to SQL , error 53). I think it has something to do with deleting Maint. Tasks outside of SSIS. Does anyone know of a solution?Thank you. |
|
|
stephe40
Posting Yak Master
218 Posts |
Posted - 2008-03-25 : 15:43:24
|
| Delete the row that represents the package from the msdb.dbo.sysdtspackages90 table. And then never use a maintenance plan again. It is not hard to write your own scripts to do backups and index rebuilds.- Eric |
 |
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-03-25 : 16:01:52
|
| how can I email a report of a given step to myself? xp_sendmail? is there a way I can script a maint. task to view how that is done?Thank you for your previous help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-25 : 23:33:14
|
| Don't mess up system tables. Can you edit plan on sql server directly? |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-03-26 : 13:38:58
|
| I can edit it but I can't get rid off it. Whatever stephe40 suggested worked though. Do you think writing a job with many steps would make more sense? I am getting a little worried about what he/she said to write scripts rather than using maint. tasks. Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-26 : 22:42:03
|
| Script works fine if you know how to write it. You can use single job with multiple steps if like to run them in same schedule. |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-03-27 : 10:40:51
|
| but is there some kind of problem with using maint. taks rather than scripting other than its not true DB administration? |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-03-27 : 14:52:37
|
I just realized that it did delete the maintenance task but it didn't delete the related jobs. How may I delete those from the context?I get a reference constraint in dbo.sysmaintplan_subplans for column job_id.  |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-27 : 23:00:50
|
| CAn you delete job under sql agent -> jobs? |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-03-28 : 12:46:31
|
| no my question is can I delete jobs under agent after I deleted the maintenance task. the command stephe40 showed me in the beginning of this thread worked but it didn't delete the corresponding jobs in the sql agent. I need to clear those up too and would like to know how I may delete them w/out getting an error. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-29 : 00:08:03
|
| Should be able to delete job. Did you get any error when try to? |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-03-31 : 10:38:05
|
| delete statement conflicted with reference constraint 'fk_subplan-job_id in msdb table dbo.sysmaintplan_subplans, column job_id. statement has been terminated. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-31 : 22:28:56
|
| Sounds subplan is still there, not good idea to mess up system tables. |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-04-01 : 10:45:15
|
| yes I kind of figured that, how may I get rid off it now? |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-04-01 : 11:45:33
|
I now completely screwed my db  Problem Description: Tried to delete a subplan with command DELETE FROM DBO.SYSMAINTPLAN_LOG WHERE SUBPLAN_ID = 'EEA9C06E-ACE3-448F-AB4B-D8BB13B3E2CB'Got an error, ran DBCC CHECKDB and got this error:Msg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.DBCC results for 'msdb'.Msg 8921, Level 16, State 1, Line 1Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'.Restored msdb and still getting this error.Operating System: Windows Server 2003 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-01 : 23:21:08
|
| Do you have previous, say couple of weeks ago, msdb backup? Worst case is recreating msdb. |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-04-02 : 11:27:03
|
| I do but I'd like to hear about recreating MSDB if you're willing to share :). |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-02 : 23:04:22
|
| Just the script INSTMSDB.SQL, you can find it in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install (may differ on your machine). |
|
|
|
dbist
Yak Posting Veteran
52 Posts |
Posted - 2008-04-03 : 12:31:34
|
| thank you.... |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-03 : 13:36:28
|
| Also, be aware, if you do rmiao's suggestion, you're going to lose your job data as it recreates it, it doesn't fix the problem you currently have. A restore from a good backup would be your best bet. And NEVER delete from system tables.Terry |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2008-04-03 : 13:38:12
|
| Should read (just so it's clear)- Also, be aware, if you do rmiao's suggestion, you're going to lose your job data as it recreates the database, it doesn't fix the problem you currently have. A restore from a good backup would be your best bet. And NEVER delete from system tables.Terry |
|
|
|
Next Page
|
Cannot Delete a Maintenance Task in Mgmt Studio