One of my maintenance plans has mysteriously disappeared out of the
management studio. Does anyone know where they are stored inside the database
and if I can get them out as a script to recreate the plan.

I have a complete backup of the system databases

Could I create an new sequel instance and restore the system databases?

Thanks


David Hills

--
Thanks



David Hills

If it disappeared then most likely someone deleted it. In any case it is
probably much easier to just recreate the plan from scratch using the
wizard.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"David Hills" <DavidHills (AT) discussions (DOT) microsoft.com> wrote

I am a programmer acting as DBA for our SQL Server 2005 and 2000
installations. Although I am comfortable with designing databases and stored
procedures, and setting up maintenance and backup schedules, I'm totally
clueless about what to do if SQL calls start timing out. I'll dutifully open
up the Activity Monitor and look at the Process Info and Locks by Process
and Object, but have little idea what I'm looking at, nor what to do if I
find something locked that shouldn't be.

Can someone point me to a good online article or book that would help teach
me this area of SQL Server administration?

Or (just a wild guess), the server is sp2 and the SSMS is pre-sp2 so
it doesn't understand the maint plan and because of that doesn't show
it. Again, just a wild guess...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Terry Carnes (tcarnes (AT) reedfax (DOT) com) writes:
First of all, in case you don't know the timeout is in the client API,
and does not happen in SQL Server (unless you are playing with lock
timeouts or use linked servers).

The cause for a timeout is one of two things:
1) The query is taking longer the time to execute than the client will
allow. The default timeout is thirty seconds.
2) Blocking.

In the first case, it may be perfectly normal, although it may also be
that the query needs tuning. But you can set the timeout on the connection
object to 0, which means wait forever. Then you, or whichever the user,
has the power decide on your own how long to wait.

In case of blocking, well that is likely to resolve sooner or later
as well, but if it is an orphaned transaction in a query window, that
typically requires manual intervention.

The quick way to check for blocking is to use sp_who or sp_who2. If
there is a row with a non-zero value in the Blk column, there is blocking
and the number in Blk is the spid for the blocker, and the spid on
that row is blocked.

If there is no blocking, then your query is taking a long time in which
case you may need to investigate query plans and indexes.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

this thread has been kidnapped!!!

Can I ask my question again

where inside the data base is the maintainance plans stored.

Can I get them out in a usable fashion some how.

I have a full system backup
--
Thanks



David Hills


"David Hills" wrote:

Good luck!

select * from msdb.dbo.sysmaintplan_subplans
select * from msdb.dbo.sysdbmaintplans
select * from msdb.dbo.sysdbmaintplan_jobs
select * from msdb.dbo.sysdbmaintplan_databases

(Or, as it has been suggested, it will be less work to re-create them from
scratch, especially if you don't find them in there.)


On 1/28/09 5:37 AM, in article
C0A5C056-D338-4E8C-A3F0-046D223CA258...soft (DOT) com, "David Hills"
<DavidHills (AT) discussions (DOT) microsoft.com> wrote: