Does SQL Server 2008 have any default mantenance plans?

views:

130

answers:

Does SQL Server 2008 have any default mantenance plans?

Hello everyone,

I am using SQL Server 2008 Enterprise version. I want to know if I do not explicitly set any maintenance jobs, are there any default maintenance jobs SQL Server will perform (like backup? rebuild index? truncate transaction log?)? Where to find the current maintenance jobs?

thanks in advance, George

+1 A:

In SSMS, in the Object Explorer, there's a Maintenance folder. There's a "Maintenance Plans" folder under there.

John Saunders 2009-07-11 13:31:32

Hi John, do you mean the maintenance plan under the management node under the whole server? I am confused because I did not find a maintenance plan under each database on the server.Here is the screen snapshot,http://i28.tinypic.com/2nuu6xd.jpg

George2 2009-07-11 13:39:47

You found them. Notice that there are no maintenance plans in there, because none are created by default.

John Saunders 2009-07-11 16:04:07

Thanks John! :-)

George2 2009-07-12 06:50:03

+1 A:

No. Out of the box SQL Server will not backup automatically nor will it do any maintenance for you. As far as truncating the transaction log goes, you can do that automatically by setting the database mode to SIMPLE, but in a production environment, you do NOT want to do this, because it breaks your transasction log backup chain. In a production environment, you will need to set up a plan to back up your transaction logs and your databases, and to do index maintenance.

Dave Markle 2009-07-11 13:35:16

Thanks Dave, if I want to check the current setup maintenance plans/jobs, I should go to the management node under the whole server of SSMS? I am confused because I did not find a maintenance plan under each database on the server. Here is the screen snapshot, http://i28.tinypic.com/2nuu6xd.jpg

George2 2009-07-11 13:41:26

If you think about it, the Maint plan belongs at the server level, not at the DB level. For example, what if you wanted to take a DB offline for a while in the maint plan? Then you couldn't do that inside the database itself. You might also want to define a plan that works across databases, so this organization adds flexibility.

Dave Markle 2009-07-11 13:44:55

Your answer makes senses Dave. If the maintenance plan folder is empty, it means no maintenance jobs currently set? No other hidden maintenance jobs? :-)

George2 2009-07-11 13:53:51

That's correct. That means there are no other hidden maintenance jobs. Of course, that doesn't mean that your DBA hasn't set up a script that runs under a scheduled task or the SQL Server Agent which essentially carries out the same steps. Unless, of course, you're looking at a new server, in which case no, there's nothing else.

Dave Markle 2009-07-11 14:02:11

Thanks Dave, my question answered. You are so knowledgable.

George2 2009-07-11 14:06:47

ansaurus

tags:

views:

answers:

Does SQL Server 2008 have any default mantenance plans?

related questions