Maintenance Plans

We just started using DPM 2007 to backup our 2005 DB's so I am deleting the
backups in the maintenance plans. Should I go back in and have any regular
maintenance on DB's like Shrink or Reorganize or is just backing up using DPM
ok?
DPM only does backups and AFAIK it also takes care of the retention period
as well. But it does not handle any other routine database functions such as
CHECKDB's, Reindexing etc. so you still have to do that yourself. And
shrinking should not be one of them.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:ED8523CE-150E-4576-AFF0-0D9AD6B89EAB@.microsoft.com...
> We just started using DPM 2007 to backup our 2005 DB's so I am deleting
> the
> backups in the maintenance plans. Should I go back in and have any regular
> maintenance on DB's like Shrink or Reorganize or is just backing up using
> DPM
> ok?
>
|||Thanks Andrew. What is typical for a maintenance plan, there are many options
in the Admin. Studio.
I just set one up to:
Check Integrity
Reorg Index
Update Stats
Clean up history
Maintenance Cleanup
"Andrew J. Kelly" wrote:

> DPM only does backups and AFAIK it also takes care of the retention period
> as well. But it does not handle any other routine database functions such as
> CHECKDB's, Reindexing etc. so you still have to do that yourself. And
> shrinking should not be one of them.
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:ED8523CE-150E-4576-AFF0-0D9AD6B89EAB@.microsoft.com...
>
|||Thanks Tibor. I will get to reading to better understand what each does.
"Tibor Karaszi" wrote:

> By understanding what each option do, you can decide which options are suitable for a particular
> installation.
> For instance, Maint Cleanup can be critical if you also do backup and don't have other means to
> remove old backup files.
> Or, Reorg can be important to reduce fragmentation, but since the maint plan's reorg is stupid and
> don't take into account initial fragmentation one might want to skip this step and do it yourself
> (smarter). Or perhaps not at all, if fragmentation don't cost you.
> etc...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:29C1FCC7-A476-4474-AB91-541A8CC16C00@.microsoft.com...
>

Maintenance Plan,SQL Server 2000

Hi,
I set up a scheduled Maintenance Plan for DB backups. For some reason just user DBs backups are performed. For master and msdb I can run backups manually via T-SQL or Enterprise Mngr and do not have any problems. But it doesn't work with Maint. Plan. There are no any error messages in SQL error log, the only error message I can see comes from View Job History(SQL Server Agent, Jobs) and says 'job failed'.
I administrate several servers and never had this kind of problem.
I logged as 'sa' .
Any ideas?
I appreciate all your help.:confused:Any chance you can post the command(s) you are using to backup your user and system dbs?|||Thank you for your response. I've fixed the problem - it was a security issue for the NT account on master & msdb files.
Thanx again

maintenance plan reports delete backup file fail but deletes it ok

Hi
I have a sql 2000 sp4 maintenance plan that is set to delete old dbs
over 2 days old. In the maintenance plan history it reports this
activity failing for one db, but the Message section reports 1 file
deleted. The transaction log backup delete works ok and reports
correctly. Any ideas?
KevHey Kev,
(Don'cha hate it when no one replies to your question') :-)
Even though the History states failure, does the physical file actually get
deleted, though? I've seen several times the "work" gets done even though
sqlmaint (for some reason) has problems and fails. I've never really paid
much attention to the History, myself. Maybe I should. But I always
right-click on the job (if it show failure) and check the jobsteps. Then, I
check the output file created by the Plan's job (backup, optimization, etc.).
Could you send the few lines of output from both the History (may need to
manually type) and the Message section of the output? Manybe I can see
something from them.
Thanks
TJ
"nsiadmin" wrote:
> Hi
> I have a sql 2000 sp4 maintenance plan that is set to delete old dbs
> over 2 days old. In the maintenance plan history it reports this
> activity failing for one db, but the Message section reports 1 file
> deleted. The transaction log backup delete works ok and reports
> correctly. Any ideas?
> Kev
>|||On 21 Mar, 14:50, ToeJam <Toe...@.discussions.microsoft.com> wrote:
> Hey Kev,
> (Don'cha hate it when no one replies to your question') :-)
> Even though the History states failure, does the physical file actually get
> deleted, though? I've seen several times the "work" gets done even though
> sqlmaint (for some reason) has problems and fails. I've never really paid
> much attention to the History, myself. Maybe I should. But I always
> right-click on the job (if it show failure) and check the jobsteps. Then, I
> check the output file created by the Plan's job (backup, optimization, etc.).
> Could you send the few lines of output from both the History (may need to
> manually type) and the Message section of the output? Manybe I can see
> something from them.
> Thanks
> TJ
> "nsiadmin" wrote:
> > Hi
> > I have a sql 2000 sp4 maintenance plan that is set to delete old dbs
> > over 2 days old. In the maintenance plan history it reports this
> > activity failing for one db, but the Message section reports 1 file
> > deleted. The transaction log backup delete works ok and reports
> > correctly. Any ideas?
> > Kev
Hi TJ
thanks for your reply.
this is the log file from the maint job :
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'MHA-SQL01\SQL01' as 'MHACAREGROUP\CLU01SQLSVC' (trusted)
Starting maintenance plan 'MHA CareG1_SITE Mtnce plan' on 19/03/2007
20:00:02
[1] Database MHACareG1_SITE: Transaction Log Backup...
Destination: [G:\Program Files\Microsoft SQL Server\MSSQL
$SQL01\BACKUP\MHACareG1_SITE\MHACareG1_SITE_tlog_200703192000.TRN]
** Execution Time: 0 hrs, 0 mins, 9 secs **
[2] Database MHACareG1_SITE: Delete Old Backup Files...
2 file(s) deleted.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'MHA CareG1_SITE Mtnce plan' on 19/03/2007
20:00:11
SQLMAINT.EXE Process Exit Code: 0 (Success)
yet the maintenance plan history records :
Db Activity Status
End date Message
MHACareG1_site Delete old db backups X 19/03/07 20:31
2 files deleted
so it appears to do exactly what it says, but still reports it as
failed.
I am going to set up a new maint plan for this db to a different
folder with exactly the same details
Kev|||That is interesting, not to mention odd.
Question; What else does this Maint. Plan do besides the TLog backup?
The job output states that the job (or step?) begins @. 20:00:02 and finishes
@. 20:00:11 (Execution Time: 9 secs). BUT, the History record shows the End
date (time) as 20:31. What else occurred within those 31 minutes? A
Database Backup, or an Optimization perhaps? Maybe it was one of those that
actually failed. Or are there any other Steps within the same Job?
TJ
"nsiadmin" wrote:
> On 21 Mar, 14:50, ToeJam <Toe...@.discussions.microsoft.com> wrote:
> > Hey Kev,
> >
> > (Don'cha hate it when no one replies to your question') :-)
> >
> > Even though the History states failure, does the physical file actually get
> > deleted, though? I've seen several times the "work" gets done even though
> > sqlmaint (for some reason) has problems and fails. I've never really paid
> > much attention to the History, myself. Maybe I should. But I always
> > right-click on the job (if it show failure) and check the jobsteps. Then, I
> > check the output file created by the Plan's job (backup, optimization, etc.).
> >
> > Could you send the few lines of output from both the History (may need to
> > manually type) and the Message section of the output? Manybe I can see
> > something from them.
> > Thanks
> > TJ
> >
> > "nsiadmin" wrote:
> > > Hi
> >
> > > I have a sql 2000 sp4 maintenance plan that is set to delete old dbs
> > > over 2 days old. In the maintenance plan history it reports this
> > > activity failing for one db, but the Message section reports 1 file
> > > deleted. The transaction log backup delete works ok and reports
> > > correctly. Any ideas?
> >
> > > Kev
> Hi TJ
> thanks for your reply.
> this is the log file from the maint job :
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'MHA-SQL01\SQL01' as 'MHACAREGROUP\CLU01SQLSVC' (trusted)
> Starting maintenance plan 'MHA CareG1_SITE Mtnce plan' on 19/03/2007
> 20:00:02
> [1] Database MHACareG1_SITE: Transaction Log Backup...
> Destination: [G:\Program Files\Microsoft SQL Server\MSSQL
> $SQL01\BACKUP\MHACareG1_SITE\MHACareG1_SITE_tlog_200703192000.TRN]
> ** Execution Time: 0 hrs, 0 mins, 9 secs **
> [2] Database MHACareG1_SITE: Delete Old Backup Files...
> 2 file(s) deleted.
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'MHA CareG1_SITE Mtnce plan' on 19/03/2007
> 20:00:11
> SQLMAINT.EXE Process Exit Code: 0 (Success)
> yet the maintenance plan history records :
> Db Activity Status End date Message
> MHACareG1_site Delete old db backups X 19/03/07 20:31 2 files deleted
> so it appears to do exactly what it says, but still reports it as
> failed.
> I am going to set up a new maint plan for this db to a different
> folder with exactly the same details
> Kev
>|||Hi Tj,
yes it does a db backup which works ok. The History record refers only
to the delete old files part of the job which ran at 20:31. All the
steps within the maint job are listed in the history as separate
activities, but only delete old files step fails.
Interestingly I set up the new job to run overnight, created 2 old
files in the destination folder, and everything worked ok.
Sometimes I guess its easier to just start again and forget it.
Cheers
Kev
On 21 Mar, 16:18, ToeJam <Toe...@.discussions.microsoft.com> wrote:
> That is interesting, not to mention odd.
> Question; What else does this Maint. Plan do besides the TLog backup?
> The job output states that the job (or step?) begins @. 20:00:02 and finishes
> @. 20:00:11 (Execution Time: 9 secs). BUT, the History record shows the End
> date (time) as 20:31. What else occurred within those 31 minutes? A
> Database Backup, or an Optimization perhaps? Maybe it was one of those that
> actually failed. Or are there any other Steps within the same Job?
> TJ
> "nsiadmin" wrote:
> > On 21 Mar, 14:50, ToeJam <Toe...@.discussions.microsoft.com> wrote:
> > > Hey Kev,
> > > (Don'cha hate it when no one replies to your question') :-)
> > > Even though the History states failure, does the physical file actually get
> > > deleted, though? I've seen several times the "work" gets done even though
> > > sqlmaint (for some reason) has problems and fails. I've never really paid
> > > much attention to the History, myself. Maybe I should. But I always
> > > right-click on the job (if it show failure) and check the jobsteps. Then, I
> > > check the output file created by the Plan's job (backup, optimization, etc.).
> > > Could you send the few lines of output from both the History (may need to
> > > manually type) and the Message section of the output? Manybe I can see
> > > something from them.
> > > Thanks
> > > TJ
> > > "nsiadmin" wrote:
> > > > Hi
> > > > I have a sql 2000 sp4 maintenance plan that is set to delete old dbs
> > > > over 2 days old. In the maintenance plan history it reports this
> > > > activity failing for one db, but the Message section reports 1 file
> > > > deleted. The transaction log backup delete works ok and reports
> > > > correctly. Any ideas?
> > > > Kev
> > Hi TJ
> > thanks for your reply.
> > this is the log file from the maint job :
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> > Server 'MHA-SQL01\SQL01' as 'MHACAREGROUP\CLU01SQLSVC' (trusted)
> > Starting maintenance plan 'MHA CareG1_SITE Mtnce plan' on 19/03/2007
> > 20:00:02
> > [1] Database MHACareG1_SITE: Transaction Log Backup...
> > Destination: [G:\Program Files\Microsoft SQL Server\MSSQL
> > $SQL01\BACKUP\MHACareG1_SITE\MHACareG1_SITE_tlog_200703192000.TRN]
> > ** Execution Time: 0 hrs, 0 mins, 9 secs **
> > [2] Database MHACareG1_SITE: Delete Old Backup Files...
> > 2 file(s) deleted.
> > Deleting old text reports... 1 file(s) deleted.
> > End of maintenance plan 'MHA CareG1_SITE Mtnce plan' on 19/03/2007
> > 20:00:11
> > SQLMAINT.EXE Process Exit Code: 0 (Success)
> > yet the maintenance plan history records :
> > Db Activity Status End date Message
> > MHACareG1_site Delete old db backups X 19/03/07 20:31 2 files deleted
> > so it appears to do exactly what it says, but still reports it as
> > failed.
> > I am going to set up a new maint plan for this db to a different
> > folder with exactly the same details
> > Kev