SQL Maintenance Plans

Question

SQL Maintenance Plans

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Tatsu SSCertifiable Points: 7824 More actions · Answer 1

I'm with you David! I hate when one database or log backup fails, it doesn't bother to do any of the backup deletes. This creates worse problems since the disk can fill up over the weekend and cause all the backups to be failing by Monday.

BTW, I don't like direct-to-tape backup systems. Response time on a restore is too slow in a large Enterprise and what happens if it didn't make it to tape in the first place? Oftentimes someone else is responsible for the tape backups and there have been a few times where I needed some backup files restored and they were not there.

[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog

gregfaulk Old Hand Points: 369 More actions · Answer 2

I don't use the wizard but I do use sqlmaint.exe. My approach is to use VBScript and SQL-DMO to loop through all the databases on a server and then conditionally generate a sqlmaint.exe command that is appropriate for that particular database. For example, I might vary the update statistics sample percentage using a smaller percentage for a large database, and a larger percentage for a smaller DB that has skewed data in important filter columns. In summary it seemed easier to use sqlmaint.exe than to attempt to perform the equivalent work using SQL-DMO or T-SQL.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

We have quite a few SQL Server installations here and I use this feature for all backups. I think it is wonderful and simple to set up. Unlike Oracle where you have to do this, do that... change this etc... The one addition I would like the wizard to more easily do is NOT have to schedule a backup, integrity check, update stats via time.. I would like to have one kick off the other, then the other etc.... I do think they should make the wizard usable for differential backups though. That is part of the reason I don't do differental backups anyway.

lozzamoore SSC Enthusiast Points: 141 More actions · Answer 4

We use Maintenance Plans a lot in our environment, consisting of around 300 smallish SQL Servers across the globe. They generally work well.

Contrary to what is stated above, there is meaningful information returned from Maintenance plan jobs when a failure occurs, but you need to know where to look. Don't look at the SQL job history-as this gives the meaningless generic error message. Instead, right click on the Maintenance Plan itself, and choose Maintenance Plan history. You can then filter by plan name, and status=failure to get a meaningful error message. This approach actually uses the plan history text files that are produced as part of the plan.

My only gripe with Maintenance plans in general is that MS in their wisdom decided not to clear down Backup History tables. This means that on servers with large amounts of DBs, or high frequency of backups, MSDB database can quickly fill up, and the plan is therefore not self maintaining(!). I'll be hoping they have included this in SQL 2005.

Thanks,

Baljeet SSC Rookie Points: 31 More actions · Answer 5

Hi,

I have your other articles also. I have to migrate SQL server from OS WINNT to WIN2K.

What are the steps involved . Is simply copy and Paste of SQL folders will work or not

Thanks

Baljeet

nmoore SSCrazy Points: 2414 More actions · Answer 6

I don't like them because of the inability to customise what they produce without annoying the wizard. The main benefit is the speed with which a beginner can set up necessary admin tasks but sure once you have the scripts its easy to adapdt them to each instance as required.

Nigel Moore
======================

Paul McGirr-296298 Valued Member Points: 59 More actions · Answer 7

I also use maintenance Plans -for convenience and also as my first backup line as I also use scripts for backups.

However recently I have started reading from the logging tables to get a daily summary of my night time backup jobs.

I push the results into a summary table and report it on a centralised web page - green for success red for fail - it provides a quick look at the sql server backups.

/*

select database_name,server_name,activity,start_time,message,succeeded,getdate() as'Inserted'

from msdb..sysdbmaintplan_history

where

start_time >= convert(datetime,convert(varchar(10),getdate()-1,121) + ' 18:00',121)

and start_time <= convert(datetime,convert(varchar(10),getdate()-1,121) + ' 23:55',121)

*/

Juanita SSCertifiable Points: 5300 More actions · Answer 8

HI.I use the maintenance plans and wanted to know if there was a specific order to run the different jobs and if I'm doing this correctly. Currently i run the maintance plans everynight. On the optimization tab, i have chosen 'reorganize data and index pages' and i have selected 'change free space per page' to the default of 10. on the Integrity tab i have chosen 'Check database integrity' and 'include indexes'. then i run the complete backup of db and tlog. I run the jobs in order of the way the tabs appear. Is this correct? I have read somewhere that the db backup should be ran 1st in case the other steps somehow corrupt the db, you can restore.

Thanks,

Juanita

Monte A Kramer SSC Enthusiast Points: 124 More actions · Answer 9

I am totally new to SQL server, therefore I have been given the responibility to set up a backup job for a small database. Short story is I set up a maintenace plan and it errors out with a message that it is refused because it has no job steps. I thought the wizard was supposed to create all that for me.

More to the point, where is the best place to begin learning SQL server 2005? Is there another topic that would be more helpful?

Thanks.

Monte

icata Hall of Fame Points: 3176 More actions · Answer 10

You might not have Integration Services installed.

Monte A Kramer SSC Enthusiast Points: 124 More actions · Answer 11

How do I know if Integration Services are installed? Would this be a service running on the server? I have installed the IS according to Microsoft's instructions on the workstation running the SSMS.

Thanks.

happycat59 One Orange Chip Points: 29389 More actions · Answer 12

Integration services runs as a service called "SQL Server Integration Services". Remember that you can install SQL Server 2005 components on your workstation as well as on your server(s). I would expect that you should be checking the server when verifying whether Integration Services has been installed.

To check whether it is installed, you can either

- run the services applet (Control Panel --> Adminstrative Tools --> Services)

- attempt to connect to Integration services using SQL Server Management Studio

- go to Add/Remove programs and check what components of SQL Server 2005 were installed.

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 13

I've got a question about this statement:

I created a plan on my test machine, here is what I ended up with for the backup job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0D097FED-F1C1-4850-A34C-7A83EF96A81B -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'

When Andy says "ended up" where exactly does he find that code?!

Jeff Shurak SSCrazy Points: 2361 More actions · Answer 14

Jeff Shurak

SSCrazy

Points: 2361

May 15, 2008 at 7:55 am

#815882

nevermind

mlemay SSC Rookie Points: 39 More actions · Answer 15

I love maintenance plans. I don't understand why anyone would not use them for at least basic functionality, though in SQL 2005 they have gotten more powerful and a bit convoluted.

I've got an environment I inherited through a corporate acquisition that tripled the amount of servers I now administer and I'd like to use the "Log to a Remote Server" option so I can centralize all of the server Maintenance Plan data. Unfortunately, I cannot find any documentation on where the data actually ends up. It allows me to setup a connection which consists of the "Connection Name", "Server Name" and logon info. I'm assuming it all ends up in MSDB somewhere, but where??