sql 2005 maintenance plans

[divinyl]

Hi all

I'm confused. I've been creating maintenance plans in the GUI and now i'd like to have a look at the information in the system tables containing the information but there's nothing in them. The database is msdb and the tables should be sysdbmaintplans, sysdbmaintplan_databases and sysdbmaintplan_jobs.. but they are empty even though i have SQL 2005 maintenance plans!

Can anyone please advise???

Thanks

Div

 

[ptheriault]

In SQL Server 2005 I believe the maint plans are saved in the resource db. There are no more system tables in any database now.

 

[divinyl]

Thanks, just did a little reading on the resource database - i hadn't heard of it before... but it says that the database should only be accessed by a microsoft specialist.. so if the maintenance plan info is in there how are we supposed to access it?????????

Div

 

[ptheriault]

Well you can't query it directly. You could copy the resource database and attach the copy as a normal SQL database. To do this you will need to do the following.

Create a copy of the mssqlsystemresource.mdf and ldf files. place it where you won't confuse it with the live db.

run the following.

EXEC sp_attach_db
'Resource_copy',
'<path to file>resource_copy.mdf',
'<path to file>resource_copy.ldf'

SQL server will now see this as a regular db and you can query it. Keep in mind this is only a copy and if you tried to change anything here you will not be changing your resource db.

 

[ptheriault]

I should also mention that you can query your live resource database if you can take your server off-line and connect as the dedicated admin. In order to do this you must put the server in single user mode and connect with the following.

Admin:<server_name>

For this to work you also must be a member of the sa role.

Once you are in you can run the following.

USE mssqlsystemresource
GO