SQLServerWiki

“The Only Thing That Is Constant Is Change”

Not able to see Maintenance plans from SQL Server 2005 Management Studio

Posted by database-wiki on March 9, 2011

Problem Description

We have created many maintenance plans, For some reason we are not able to view any maintenance plan in the management studio. We have tried to login into management studio with sysadmin privileges as well as used both windows and sql server logins. But we are not able to see all the jobs that are related to maintenance plans. When we tried to execute the jobs its fails with the below error message

Error Message

Executed as user: klip\sqlserver. …sion 9.00.3042.00 for 32-bit  Copyright

(C) Microsoft Corp 1984-2005. All rights reserved.   

Started:  10:00:00 PM  Progress: 2009-06-07 22:00:01.78    

Source: {49CCA0F1-4BD0-4E7C-AC14-559E1B0B7AA1}     

Executing query “DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp”.: 100% complete

End Progress  Progress: 2009-06-07 22:00:03.51     Source: Back Up Database (Full)     

Executing query “BACKUP DATABASE [Audit] TO  DISK = N’D:\WSS Backup”.: 100% complete 

End Progress  Progress: 2009-06-07 22:00:03.81     Source: Back Up Database (Full)     

Executing query “BACKUP DATABASE [nasa_AJ] TO  DISK = N’D:\WSS Back”.: 100% complete 

End Progress  Progress: 2009-06-07 22:00:04.08     Source: Back Up Database (Full)      

Executing query “BACKUP DATABASE [nasa_BA] TO  DISK = N’D:\WSS Back”.: 100% complete 

End Progress  Progress: 2009-06-07 22:00:04.28     Source: Back Up Database (Full)     

Executing query “BACKUP DATABASE…  The package execution fa…  The step failed.

The job failed.  The Job was invoked by Schedule 14 (kscingr2_non-SharePoint). 

The last step to run was step 1 (Subplan_1).

We wanted to know why we are not able to see the maintenance plans as well as fix the job failures.

Engineer’s Troubleshooting

We collected the msdb database backup and a profiler trace and sql server and agent logs to troubleshoot the issue further.

From the profiler trace we have determined that the below record is not returning any records hence we are not able to see any maintenance plans.

SELECT

s.name AS [Name],

‘Server[@Name=’ + quotename(CAST(serverproperty(N’Servername’) AS sysname),””) + ‘]’ + ‘/MaintenancePlan[@Name=’

+ quotename(s.name,””) + ‘]’ AS [Urn],

s.from_msx AS [FromMSX],

s.has_targets AS [HasTargets],

s.create_date AS [CreateDate],

s.owner AS [Owner]

FROM

msdb.dbo.sysmaintplan_plans AS s

ORDER BY

[Name] ASC

It’s the above query that is being fired in background when we try to expand a maintenance plan folder in management studio.

Looking at this view sysmaintplan_plans

 SELECT

   s.name AS [name],

   s.id AS [id],

   s.description AS [description],

   s.createdate AS [create_date],

   suser_sname(s.ownersid) AS [owner],

   s.vermajor AS [version_major],

   s.verminor AS [version_minor],

   s.verbuild AS [version_build],

   s.vercomments AS [version_comments],

   ISNULL((select TOP 1 msx_plan from sysmaintplan_subplans where plan_id = s.id), 0) AS [from_msx],

   CASE WHEN (NOT EXISTS (select TOP 1 msx_job_id

                          from sysmaintplan_subplans subplans, sysjobservers jobservers

                          where plan_id = s.id

                          and msx_job_id is not null

                          and subplans.msx_job_id = jobservers.job_id

                          and server_id != 0))

        then 0

        else 1 END AS [has_targets]

   FROM

   MSDB.dbo.sysdtspackages90 AS s

   WHERE

   (s.folderid = ’08aa12d5-8f98-4dab-a4fc-980b150a5dc8′ and s.packagetype = 6)

This is the definition of the view and if we look at the definition the folderid is hardcoded and in the sysdtspackagefolders90 and sysdtspackages90 table at customers end he doesn’t have a folder which is the root cause of the issue why the records are not returning since the condition is failing.

Trying to understand why this would occur and trying to reproduce the issue locally, I was able to reproduce the problem locally with the below steps.

1. Connected to Integration Services from Management studio Traversed to Stored Packages

   ==>>

     MSDB

     ==>>

     Maintenance Plans

Deleted the Maintenance Plans folder and Recreated it back with the same name

2. Go-ahead and query the sysdtspackagefolders90 table and look at the records, When we deleted the  Maintenance plan folder from integration services the default record which had the GUID

   OF ’08aa12d5-8f98-4dab-a4fc-980b150a5dc8′ for folderid column for Maintenance Plan foldername  got deleted and now a new record got inserted with a different

   GUID 7B3A6F56-5D16-4CD1-B7DB- B177F6E757B5

3. Now go-ahead and create a maintenance plan through wizard and try to expand the maintenance plan folder  for sql server instance registration and you will not be able to see any plans. This is  

    because the GUID is different and the view sysmaintplan_plans had a hard coded value for the maintenance plan folderid.

This proved that in the past we have deleted the maintenance plan folder and have recreated it which has caused all this issue.  

Since i was able to reproduce the issue i could use the below workaround to resolve the issue.

Workaround (When we have maintenance plans created)

Update msdb.dbo.sysdtspackagefolders90

SET

FOLDERID=’08aa12d5-8f98-4dab-a4fc-980b150a5dc8′

WHERE foldername=’Maintenance Plans’

go

Update msdb.dbo.sysdtspackages90

SET Folderid = ’08aa12d5-8f98-4dab-a4fc-980b150a5dc8′

WHERE s.packagetype = 6

go

Note:

Updating System Tables is not recommended and supported by Microsoft. If your are dealing with production system open a ticket with PSS to proceed further on this issue.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: