March 31, 2008 at 8:50 am
Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below. (I hope this is the appropriate forum for them.)
I've recently been given responsibility for a pair of clustered Windows 2003 servers running SQL Server 2005. Looking through SQL Server Management Studio, I found the following three maintenance plans:
- System Maintenance Plan (all system databases)
- Plan 1 (1 database)
- Plan 2 (4 databases).
All three maintenance plans had the following elements configured, in the following order:
- Check Database Integrity
- Backup Database (Full)
- Shrink Database
- Update statistics
- Clean Up History
with the exception that Plan 1 doesn't have a 'Shrink Database' task (because, I'm told, its data is pretty static), but none had a Maintenance Cleanup Task, so I added one to each plan specifying 14 days of old backups to be kept in all cases except Plan 1, where I limited it to 2 days, the database being over 7 GB in size. All links between tasks in all plans are AND constraints, value 'Completion'. All .BAKs are written to tape by Symantec Backup Exec as part of the daily backup. Two questions from this:
1. Since .BAKs are written to separate folders for each database, do I need, for example, four Maintenance Cleanup Tasks for the Plans covering four databases - one for each folder/database? I can't see a way of specifying that a maintenance Cleanup Task
should apply to multiple databases or search subfolders based on database name, so I assume I need four consecutive Maintenance Cleanup Tasks within a single plan? Is this the way to do it?
2.The data displayed when I right-click a maintenance plan and choose View History shows:
date and time run
plan name
task name
duration
log type
log source
Since I added a Maintenance Cleanup Task to each plan, the only task shown in the Task Name column is Cleanup History (set to 'Older than 7 days'), and the duration is 00:00:00, where previously all tasks were shown and the overall duration was over a minute.
However, the .BAK files are being created (size looks OK), the application log contains entries suggesting the backups and other tasks completed successfully, and if I expand the SQL Server Logs node and view the current file I can see entries which give the same 'success' messages (presumably this log is the source of the 'success' messages in the application log?).
Why does there appear to be an anomaly between the 'View History' display and the SQL Server Log and application log? Should I be worried, or is what I can see in the SQL Server Logs and application log sufficient grounds for a good night's sleep?
Thanks,
Ed
April 2, 2008 at 6:01 am
From what I see with my maintenance plans, when you add the clean up task it takes care of all databases listed in that maintenance plan. So you would not need to create a cleanup task for each database.
As for your second question, I modified one of my jobs and everything appeared correct. Since the backups seem to be working fine, I don't think I would be that concerned about the reporting aspects.
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
April 2, 2008 at 6:34 am
Wow, 14 days is a lot, but if you need it, or feel comfortable, then use it.
If you are post SP2, then the maintenance plan has a checkbox for "first level subfolders". If you check that it will work. If not, it only checks the root folder.
As far as I know the success messages should be the same in the SQL area and the event logs.
April 3, 2008 at 3:30 am
Thanks, Ray and Steve, for your replies. Very much appreciated.
May 8, 2008 at 11:57 am
I'd take the shrink out of the plans. Unless you purge a lot of data out of those DBs, shrinking will do more harm than good. SQL will grow files for a reason and you'll potentially end up with fragmentation and performance degradation if you keep trying to shrink.
May 9, 2008 at 12:17 am
Seconded. Shrink = really bad idea.
It also appears you don't have an index rebuild happening anywhere. Is that the case?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply