Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
edtraviles
Starting Member
7 Posts |
 Posted - 2008-03-31 : 10:39:44
|
| Hi. I'm very new to SQL Server and would greatly appreciate any advice with regard to my two questions below.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 maintenace plans had the following elements configured, in the following order:- Check Database Integrity- Backup Database (Full)- Shrink Database- Update statistics- Clean Up Historywith 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 runplan nametask namedurationlog typelog sourceSince 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 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-31 : 23:44:20
|
| 1. there is checkbox to include clean up backup files in subdirectory.2. did you see maintenance cleanup job under sql agent?Remove shrink db task. |
 |
|
|
edtraviles
Starting Member
7 Posts |
Posted - 2008-04-01 : 11:48:26
|
| Thanks for the reply.1. I can find a check box for 'Create a sub-directory for each database' in the Backup task, but not a 'search subdirectories' tick box in the Mtce Cleanup Task.2. If I expand the SQL Server Agent node I see- Jobs System Plan Plan 1 Plan 2If I right-click Jobs and choose View History, the Log File Viewer opens, and shows two lines for each mtce plan.The first line says '(Job outcome)' in the Step Name column, and in the Message column says that the job succeeded, and that the last Step run was 'step 1 (Subplan).The second line says 'Subplan' in the Step Name column, and in the Message column says, 'The package executed successfully. The step succeeded.' Durations given seem plausible, and all the SQL severities are 0.To a novice like me, this seems to support the conclusion that all is OK, though I still don't understand why the detail in the Log File Viewer as seen from the Management\Mtce Plan node is less than it was.Please could you explain the reason for removing the Shrink db task, so I can explain why I've done it? Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-01 : 23:43:07
|
| 1. find 'include first-level subfolders' checkbox.2. you can rename plan1 and so to meaningful name, and find plan details in plan's log file instead of job history.3. shrinking db affects performance, only do it when facing disk space issue. |
|
|
|
edtraviles
Starting Member
7 Posts |
Posted - 2008-04-03 : 06:29:01
|
| I think I'll need to install SP2 before the 'include first-level subfolders'checkbox will appear.Thanks very much for all the advice. Much appreciated. |
|
|
|
|
|
|
|
|
1. Maintenance cleanup. 2. 'View History' anomaly