Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.
Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.
|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
to[vbcol=seagreen]
also[vbcol=seagreen]
that
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
> >Quick question, I have a maintinence job that optimizes (reorganizes back
to
> >original size my databases back to the original amount of free space). I
also
> >have jobs that defrag specific tables.
> >
> >Will the maintinence job defrag as well as reorgainize.
> >
> >I would like to get rid of my defrag jobs and have one maintinence job
that
> >does both.
>

Maintinence Jobs vs Defag Jobs

Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I als
o
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:

>Quick question, I have a maintinence job that optimizes (reorganizes back t
o
>original size my databases back to the original amount of free space). I al
so
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.
4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
>
to[vbcol=seagreen]
also[vbcol=seagreen]
that[vbcol=seagreen]
>

Maintenence Plan Questions

I wanted to know at what time should I be scheduling the data optimization and data integrity jobs? Will these jobs hinder performance? If so then I should probably schedule the jobs after work hours.

Also, we were not going to use the backup/recovery jobs that sql server offer, we have our own backup software. So, will the data integrity or data optimization job affect backups? Should I perform these jobs before or after backups?

Thanks,
LauraThat is a good plan. The optimizations are NORMALLY non-intrusive, but particularly the first time they are run after significant changes they can hit the database like a freight train.

Your plan to start the optimizations after the backup finishes is a good one.

-PatP|||I would recommend not to use 3rd-party backup tools (unless it's SQLLiteSpeed) to backup live database/transaction log. Use native or purchase SQLLiteSpeed to create OS backup files, and only then use other tools to backup to a tape, for example.|||Ok thanks for the responses, it helped me alot

-laura

Maintenance Plans won't allow me to select to delete jobs after 3 days

SQL Server 2000 (SP3A) won't allow me to select to delete any jobs older tha
n 3 days. I don't get the option in the drop down box. When I change to 3
it goes back to 0 and again no option in the drop down box.
I manually went in to job (steps tab) and inserted DAYS next to the 3 and ra
n a sql backup job after this to see if it would delete the jobs older than
3 days. It didn't delete any of them. This is occurring on both the .bak a
nd .trn files, however on t
he reports it is allowing me to select both number of days and days themselv
e. (currently set to 5 days)
This is frustrating as we are now manually deleting jobs so they don't run o
ut of hard drive space when performining nightly backup and maintenance on t
he sql server.
Any help is greatly appreciated. Permissions have been checked etc and ever
ything appears to be accurate.did you try to purge backup files or jobs older than 3 days?
1. maintenance plan will purge backup or trans log backup files after
certain days if you spcify it.
2. it does not purge jobs!
3. why would you create a job then purge it 3 days later?
Steve
"Sheri" <sheri@.isdesign.com> wrote in message
news:F868C089-CDF3-4F25-B750-6227AEBFEE12@.microsoft.com...
> SQL Server 2000 (SP3A) won't allow me to select to delete any jobs older
than 3 days. I don't get the option in the drop down box. When I change to
3 it goes back to 0 and again no option in the drop down box.
> I manually went in to job (steps tab) and inserted DAYS next to the 3 and
ran a sql backup job after this to see if it would delete the jobs older
than 3 days. It didn't delete any of them. This is occurring on both the
.bak and .trn files, however on the reports it is allowing me to select bot
h
number of days and days themselve. (currently set to 5 days)
> This is frustrating as we are now manually deleting jobs so they don't run
out of hard drive space when performining nightly backup and maintenance on
the sql server.
> Any help is greatly appreciated. Permissions have been checked etc and
everything appears to be accurate.
>

Maintenance Plans and Delete files

We have been experiencing a "problem" with errors running Maintenance Plan
Jobs.
This is a Transaction Log and Full Backup Maintenance Plan.
Below is the code for the Full Backup Job step...............
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia DISK -BkUpDB
"L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
We are deleting backups over 36 hours old because we cannot trust the end
time of the previous backup. The SQLServer Agent Job shows a failure for the
MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I have
possibly traced it back to the "DELETE FILES" step of the MaintPlan. This
appears to fail with no error in the MaintPlan History.
The free drivespace is tight, there is slightly more room (~1gb) than 3
fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
needs at least enough room to create the new backup before it can delete the
oldest backup. The Backup succeeds, but the delete fails (leaves behind the
oldest backup, total 3 fulls, should be 2), the job fails, but the MaitPlan
doesn't and there is no record of a delete or any attempt to delete or an
error. My guess is that if a delete does not happen for any reason, a
MaintPlan history entry for the delete step is not made.
Any ideas..............
Frankm
You know you are in trouble when a
learning curve becomes a death spiralMake sure you don't have any of the Expire or Retain Dates set but here is
a pretty good overview of why this usually happens by Bill from MS:
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> We have been experiencing a "problem" with errors running Maintenance Plan
> Jobs.
> This is a Transaction Log and Full Backup Maintenance Plan.
> Below is the code for the Full Backup Job step...............
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
DISK -BkUpDB
> "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> We are deleting backups over 36 hours old because we cannot trust the end
> time of the previous backup. The SQLServer Agent Job shows a failure for
the
> MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I have
> possibly traced it back to the "DELETE FILES" step of the MaintPlan. This
> appears to fail with no error in the MaintPlan History.
> The free drivespace is tight, there is slightly more room (~1gb) than 3
> fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
> needs at least enough room to create the new backup before it can delete
the
> oldest backup. The Backup succeeds, but the delete fails (leaves behind
the
> oldest backup, total 3 fulls, should be 2), the job fails, but the
MaitPlan
> doesn't and there is no record of a delete or any attempt to delete or an
> error. My guess is that if a delete does not happen for any reason, a
> MaintPlan history entry for the delete step is not made.
> Any ideas..............
> Frankm
> You know you are in trouble when a
> learning curve becomes a death spiral
>|||SQL Server service and SQL Server Agent both run under the same domain login
that has local admin rights on the machine. There are no problems logging in
with that login and doing anything, we do it everyday. The job owner is also
that account.
This does not happen all the time, only occasionally. The fact that the
DELETE FILES fails is one part. There is also the problem with the fact that
there is a job failure with a message pointing to the sqlmaint.exe, but
there is absolutely no indication of any failure at the Maintenance Plan.
Good point about another process holding the file(s). I will check to see if
another process has the file, but I am unclear as to why the Maintenance
Plan does not show an error.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> Make sure you don't have any of the Expire or Retain Dates set but here is
> a pretty good overview of why this usually happens by Bill from MS:
>
> -- Log files don't delete --
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
to
> local drive), then you will need to modify the above (with respect to
where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > We have been experiencing a "problem" with errors running Maintenance
Plan
> > Jobs.
> > This is a Transaction Log and Full Backup Maintenance Plan.
> > Below is the code for the Full Backup Job step...............
> > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> DISK -BkUpDB
> > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> >
> > We are deleting backups over 36 hours old because we cannot trust the
end
> > time of the previous backup. The SQLServer Agent Job shows a failure for
> the
> > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
have
> > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
This
> > appears to fail with no error in the MaintPlan History.
> >
> > The free drivespace is tight, there is slightly more room (~1gb) than 3
> > fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
> > needs at least enough room to create the new backup before it can delete
> the
> > oldest backup. The Backup succeeds, but the delete fails (leaves behind
> the
> > oldest backup, total 3 fulls, should be 2), the job fails, but the
> MaitPlan
> > doesn't and there is no record of a delete or any attempt to delete or
an
> > error. My guess is that if a delete does not happen for any reason, a
> > MaintPlan history entry for the delete step is not made.
> >
> > Any ideas..............
> >
> > Frankm
> > You know you are in trouble when a
> > learning curve becomes a death spiral
> >
> >
>|||The lack of traceability with the maintenance plan is one of the many
reasons I never recommend using it. If you created your own scheduled jobs
to do those tasks you would have a much better grasp on the situation when
things go wrong.
--
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> SQL Server service and SQL Server Agent both run under the same domain
login
> that has local admin rights on the machine. There are no problems logging
in
> with that login and doing anything, we do it everyday. The job owner is
also
> that account.
> This does not happen all the time, only occasionally. The fact that the
> DELETE FILES fails is one part. There is also the problem with the fact
that
> there is a job failure with a message pointing to the sqlmaint.exe, but
> there is absolutely no indication of any failure at the Maintenance Plan.
> Good point about another process holding the file(s). I will check to see
if
> another process has the file, but I am unclear as to why the Maintenance
> Plan does not show an error.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > Make sure you don't have any of the Expire or Retain Dates set but here
is
> > a pretty good overview of why this usually happens by Bill from MS:
> >
> >
> > -- Log files don't delete --
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> >
> > This is likely to be either a permissions problem or a sharing violation
> > problem. The maintenance plan is run as a job, and jobs are run by the
> > SQLServerAgent service.
> >
> > Permissions:
> > 1. Determine the startup account for the SQLServerAgent service
> > (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> > account is the security context for jobs, and thus the maintenance plan.
> > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
domain
> > account) then skip step 3.
> > 3. On that box, log onto NT as that account. Using Explorer, attempt to
> > delete an expired backup. If that succeeds then go to Sharing Violation
> > section.
> > 4. Log onto NT with an account that is an administrator and use Explorer
> to
> > look at the Properties|Security of the folder (where the backups reside)
> > and ensure the SQLServerAgent startup account has Full Control. If the
> > SQLServerAgent startup account is LocalSystem, then the account to
> consider
> > is SYSTEM.
> > 5. In NT, if an account is a member of an NT group, and if that group
has
> > Access is Denied, then that account will have Access is Denied, even if
> > that account is also a member of the Administrators group. Thus you may
> > need to check group permissions (if the Startup Account is a member of a
> > group).
> > 6. Keep in mind that permissions (by default) are inherited from a
parent
> > folder. Thus, if the backups are stored in C:\bak, and if someone had
> > denied permission to the SQLServerAgent startup account for C:\, then
> > C:\bak will inherit access is denied.
> >
> > Sharing violation:
> > This is likely to be rooted in a timing issue, with the most likely
cause
> > being another scheduled process (such as NT Backup or Anti-Virus
software)
> > having the backup file open at the time when the SQLServerAgent (i.e.,
the
> > maintenance plan job) tried to delete it.
> > 1. Download filemon and handle from www.sysinternals.com.
> > 2. I am not sure whether filemon can be scheduled, or you might be able
to
> > use NT scheduling services to start filemon just before the maintenance
> > plan job is started, but the filemon log can become very large, so it
> would
> > be best to start it some short time before the maintenance plan starts.
> > 3. Inspect the filemon log for another process that has that backup file
> > open (if your lucky enough to have started filemon before this other
> > process grabs the backup folder), and inspect the log for the results
when
> > the SQLServerAgent agent attempts to open that same file.
> > 4. Schedule the job or that other process to do their work at different
> > times.
> > 5. You can use the handle utility if you are around at the time when the
> > job is scheduled to run.
> >
> > If the backup files are going to a \\share or a mapped drive (as opposed
> to
> > local drive), then you will need to modify the above (with respect to
> where
> > the tests and utilities are run).
> >
> > Finally, inspection of the maintenance plan's history report might be
> > useful.
> >
> > Thanks,
> >
> > Bill Hollinshead
> > Microsoft, SQL Server
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > We have been experiencing a "problem" with errors running Maintenance
> Plan
> > > Jobs.
> > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > Below is the code for the Full Backup Job step...............
> > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > DISK -BkUpDB
> > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > >
> > > We are deleting backups over 36 hours old because we cannot trust the
> end
> > > time of the previous backup. The SQLServer Agent Job shows a failure
for
> > the
> > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
> have
> > > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
> This
> > > appears to fail with no error in the MaintPlan History.
> > >
> > > The free drivespace is tight, there is slightly more room (~1gb) than
3
> > > fulls of the largest backup(~20gb each) (we are saving 2). The
MaintPlan
> > > needs at least enough room to create the new backup before it can
delete
> > the
> > > oldest backup. The Backup succeeds, but the delete fails (leaves
behind
> > the
> > > oldest backup, total 3 fulls, should be 2), the job fails, but the
> > MaitPlan
> > > doesn't and there is no record of a delete or any attempt to delete or
> an
> > > error. My guess is that if a delete does not happen for any reason, a
> > > MaintPlan history entry for the delete step is not made.
> > >
> > > Any ideas..............
> > >
> > > Frankm
> > > You know you are in trouble when a
> > > learning curve becomes a death spiral
> > >
> > >
> >
> >
>|||I am not sure if you were thinking of the MVP's as being part of the
Microsoft respondents but did want to make sure people understand we are not
actually employees of MS. We are all just volunteers that have been
acknowledged by MS in the MVP program. In either case I also want to point
out that there are lots of good answers from people other than MS or MVP's
that shouldn't go unrecognized either<g>.
Thanks
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Yep, In previous jobs I did my own scripting, but in this job they use
> MaintPlans almost
> exclusively. I guess it's because all you have to do is clickity,
clickity,
> click.... and voila!
> Thanks, I now know that the lack of a error message is a feature, and
adjust
> accordingly..
> *****************************************
> To Microsoft respondents on these newsgroups... Your help is GREATLY
> appreciated and
> worth every penny. It really helps to be able to ask questions, get
answers
> and get on with my job.
> Thanks to all....................................
> *****************************************
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > The lack of traceability with the maintenance plan is one of the many
> > reasons I never recommend using it. If you created your own scheduled
> jobs
> > to do those tasks you would have a much better grasp on the situation
when
> > things go wrong.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > SQL Server service and SQL Server Agent both run under the same domain
> > login
> > > that has local admin rights on the machine. There are no problems
> logging
> > in
> > > with that login and doing anything, we do it everyday. The job owner
is
> > also
> > > that account.
> > > This does not happen all the time, only occasionally. The fact that
the
> > > DELETE FILES fails is one part. There is also the problem with the
fact
> > that
> > > there is a job failure with a message pointing to the sqlmaint.exe,
but
> > > there is absolutely no indication of any failure at the Maintenance
> Plan.
> > >
> > > Good point about another process holding the file(s). I will check to
> see
> > if
> > > another process has the file, but I am unclear as to why the
Maintenance
> > > Plan does not show an error.
> > >
> > >
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > Make sure you don't have any of the Expire or Retain Dates set but
> here
> > is
> > > > a pretty good overview of why this usually happens by Bill from MS:
> > > >
> > > >
> > > > -- Log files don't delete --
> > > >
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > >
> > > > This is likely to be either a permissions problem or a sharing
> violation
> > > > problem. The maintenance plan is run as a job, and jobs are run by
the
> > > > SQLServerAgent service.
> > > >
> > > > Permissions:
> > > > 1. Determine the startup account for the SQLServerAgent service
> > > > (Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup).
> > > This
> > > > account is the security context for jobs, and thus the maintenance
> plan.
> > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
> > domain
> > > > account) then skip step 3.
> > > > 3. On that box, log onto NT as that account. Using Explorer, attempt
> to
> > > > delete an expired backup. If that succeeds then go to Sharing
> Violation
> > > > section.
> > > > 4. Log onto NT with an account that is an administrator and use
> Explorer
> > > to
> > > > look at the Properties|Security of the folder (where the backups
> reside)
> > > > and ensure the SQLServerAgent startup account has Full Control. If
the
> > > > SQLServerAgent startup account is LocalSystem, then the account to
> > > consider
> > > > is SYSTEM.
> > > > 5. In NT, if an account is a member of an NT group, and if that
group
> > has
> > > > Access is Denied, then that account will have Access is Denied, even
> if
> > > > that account is also a member of the Administrators group. Thus you
> may
> > > > need to check group permissions (if the Startup Account is a member
of
> a
> > > > group).
> > > > 6. Keep in mind that permissions (by default) are inherited from a
> > parent
> > > > folder. Thus, if the backups are stored in C:\bak, and if someone
had
> > > > denied permission to the SQLServerAgent startup account for C:\,
then
> > > > C:\bak will inherit access is denied.
> > > >
> > > > Sharing violation:
> > > > This is likely to be rooted in a timing issue, with the most likely
> > cause
> > > > being another scheduled process (such as NT Backup or Anti-Virus
> > software)
> > > > having the backup file open at the time when the SQLServerAgent
(i.e.,
> > the
> > > > maintenance plan job) tried to delete it.
> > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > 2. I am not sure whether filemon can be scheduled, or you might be
> able
> > to
> > > > use NT scheduling services to start filemon just before the
> maintenance
> > > > plan job is started, but the filemon log can become very large, so
it
> > > would
> > > > be best to start it some short time before the maintenance plan
> starts.
> > > > 3. Inspect the filemon log for another process that has that backup
> file
> > > > open (if your lucky enough to have started filemon before this other
> > > > process grabs the backup folder), and inspect the log for the
results
> > when
> > > > the SQLServerAgent agent attempts to open that same file.
> > > > 4. Schedule the job or that other process to do their work at
> different
> > > > times.
> > > > 5. You can use the handle utility if you are around at the time when
> the
> > > > job is scheduled to run.
> > > >
> > > > If the backup files are going to a \\share or a mapped drive (as
> opposed
> > > to
> > > > local drive), then you will need to modify the above (with respect
to
> > > where
> > > > the tests and utilities are run).
> > > >
> > > > Finally, inspection of the maintenance plan's history report might
be
> > > > useful.
> > > >
> > > > Thanks,
> > > >
> > > > Bill Hollinshead
> > > > Microsoft, SQL Server
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > We have been experiencing a "problem" with errors running
> Maintenance
> > > Plan
> > > > > Jobs.
> > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > Below is the code for the Full Backup Job step...............
> > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > DISK -BkUpDB
> > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > >
> > > > > We are deleting backups over 36 hours old because we cannot trust
> the
> > > end
> > > > > time of the previous backup. The SQLServer Agent Job shows a
failure
> > for
> > > > the
> > > > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan.
I
> > > have
> > > > > possibly traced it back to the "DELETE FILES" step of the
MaintPlan.
> > > This
> > > > > appears to fail with no error in the MaintPlan History.
> > > > >
> > > > > The free drivespace is tight, there is slightly more room (~1gb)
> than
> > 3
> > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > MaintPlan
> > > > > needs at least enough room to create the new backup before it can
> > delete
> > > > the
> > > > > oldest backup. The Backup succeeds, but the delete fails (leaves
> > behind
> > > > the
> > > > > oldest backup, total 3 fulls, should be 2), the job fails, but the
> > > > MaitPlan
> > > > > doesn't and there is no record of a delete or any attempt to
delete
> or
> > > an
> > > > > error. My guess is that if a delete does not happen for any
reason,
> a
> > > > > MaintPlan history entry for the delete step is not made.
> > > > >
> > > > > Any ideas..............
> > > > >
> > > > > Frankm
> > > > > You know you are in trouble when a
> > > > > learning curve becomes a death spiral
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I didn't mean to slight anyone. I was unaware of the status and
affiliation of people responding to questions.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eWMgYZTXDHA.2328@.TK2MSFTNGP12.phx.gbl...
> I am not sure if you were thinking of the MVP's as being part of the
> Microsoft respondents but did want to make sure people understand we are
not
> actually employees of MS. We are all just volunteers that have been
> acknowledged by MS in the MVP program. In either case I also want to
point
> out that there are lots of good answers from people other than MS or MVP's
> that shouldn't go unrecognized either<g>.
> Thanks
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > Yep, In previous jobs I did my own scripting, but in this job they use
> > MaintPlans almost
> > exclusively. I guess it's because all you have to do is clickity,
> clickity,
> > click.... and voila!
> >
> > Thanks, I now know that the lack of a error message is a feature, and
> adjust
> > accordingly..
> >
> > *****************************************
> > To Microsoft respondents on these newsgroups... Your help is GREATLY
> > appreciated and
> > worth every penny. It really helps to be able to ask questions, get
> answers
> > and get on with my job.
> > Thanks to all....................................
> > *****************************************
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > > The lack of traceability with the maintenance plan is one of the many
> > > reasons I never recommend using it. If you created your own
scheduled
> > jobs
> > > to do those tasks you would have a much better grasp on the situation
> when
> > > things go wrong.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > SQL Server service and SQL Server Agent both run under the same
domain
> > > login
> > > > that has local admin rights on the machine. There are no problems
> > logging
> > > in
> > > > with that login and doing anything, we do it everyday. The job owner
> is
> > > also
> > > > that account.
> > > > This does not happen all the time, only occasionally. The fact that
> the
> > > > DELETE FILES fails is one part. There is also the problem with the
> fact
> > > that
> > > > there is a job failure with a message pointing to the sqlmaint.exe,
> but
> > > > there is absolutely no indication of any failure at the Maintenance
> > Plan.
> > > >
> > > > Good point about another process holding the file(s). I will check
to
> > see
> > > if
> > > > another process has the file, but I am unclear as to why the
> Maintenance
> > > > Plan does not show an error.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > > Make sure you don't have any of the Expire or Retain Dates set but
> > here
> > > is
> > > > > a pretty good overview of why this usually happens by Bill from
MS:
> > > > >
> > > > >
> > > > > -- Log files don't delete --
> > > > >
> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > > >
> > > > > This is likely to be either a permissions problem or a sharing
> > violation
> > > > > problem. The maintenance plan is run as a job, and jobs are run by
> the
> > > > > SQLServerAgent service.
> > > > >
> > > > > Permissions:
> > > > > 1. Determine the startup account for the SQLServerAgent service
> > > > > (Start|Programs|Administrative
> tools|Services|SQLServerAgent|Startup).
> > > > This
> > > > > account is the security context for jobs, and thus the maintenance
> > plan.
> > > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
> > > domain
> > > > > account) then skip step 3.
> > > > > 3. On that box, log onto NT as that account. Using Explorer,
attempt
> > to
> > > > > delete an expired backup. If that succeeds then go to Sharing
> > Violation
> > > > > section.
> > > > > 4. Log onto NT with an account that is an administrator and use
> > Explorer
> > > > to
> > > > > look at the Properties|Security of the folder (where the backups
> > reside)
> > > > > and ensure the SQLServerAgent startup account has Full Control. If
> the
> > > > > SQLServerAgent startup account is LocalSystem, then the account to
> > > > consider
> > > > > is SYSTEM.
> > > > > 5. In NT, if an account is a member of an NT group, and if that
> group
> > > has
> > > > > Access is Denied, then that account will have Access is Denied,
even
> > if
> > > > > that account is also a member of the Administrators group. Thus
you
> > may
> > > > > need to check group permissions (if the Startup Account is a
member
> of
> > a
> > > > > group).
> > > > > 6. Keep in mind that permissions (by default) are inherited from a
> > > parent
> > > > > folder. Thus, if the backups are stored in C:\bak, and if someone
> had
> > > > > denied permission to the SQLServerAgent startup account for C:\,
> then
> > > > > C:\bak will inherit access is denied.
> > > > >
> > > > > Sharing violation:
> > > > > This is likely to be rooted in a timing issue, with the most
likely
> > > cause
> > > > > being another scheduled process (such as NT Backup or Anti-Virus
> > > software)
> > > > > having the backup file open at the time when the SQLServerAgent
> (i.e.,
> > > the
> > > > > maintenance plan job) tried to delete it.
> > > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > > 2. I am not sure whether filemon can be scheduled, or you might be
> > able
> > > to
> > > > > use NT scheduling services to start filemon just before the
> > maintenance
> > > > > plan job is started, but the filemon log can become very large, so
> it
> > > > would
> > > > > be best to start it some short time before the maintenance plan
> > starts.
> > > > > 3. Inspect the filemon log for another process that has that
backup
> > file
> > > > > open (if your lucky enough to have started filemon before this
other
> > > > > process grabs the backup folder), and inspect the log for the
> results
> > > when
> > > > > the SQLServerAgent agent attempts to open that same file.
> > > > > 4. Schedule the job or that other process to do their work at
> > different
> > > > > times.
> > > > > 5. You can use the handle utility if you are around at the time
when
> > the
> > > > > job is scheduled to run.
> > > > >
> > > > > If the backup files are going to a \\share or a mapped drive (as
> > opposed
> > > > to
> > > > > local drive), then you will need to modify the above (with respect
> to
> > > > where
> > > > > the tests and utilities are run).
> > > > >
> > > > > Finally, inspection of the maintenance plan's history report might
> be
> > > > > useful.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Bill Hollinshead
> > > > > Microsoft, SQL Server
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > > We have been experiencing a "problem" with errors running
> > Maintenance
> > > > Plan
> > > > > > Jobs.
> > > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > > Below is the code for the Full Backup Job
step...............
> > > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > > DISK -BkUpDB
> > > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > > >
> > > > > > We are deleting backups over 36 hours old because we cannot
trust
> > the
> > > > end
> > > > > > time of the previous backup. The SQLServer Agent Job shows a
> failure
> > > for
> > > > > the
> > > > > > MaintPlan (sqlmaint.exe) but there is no failure in the
MaintPlan.
> I
> > > > have
> > > > > > possibly traced it back to the "DELETE FILES" step of the
> MaintPlan.
> > > > This
> > > > > > appears to fail with no error in the MaintPlan History.
> > > > > >
> > > > > > The free drivespace is tight, there is slightly more room (~1gb)
> > than
> > > 3
> > > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > > MaintPlan
> > > > > > needs at least enough room to create the new backup before it
can
> > > delete
> > > > > the
> > > > > > oldest backup. The Backup succeeds, but the delete fails (leaves
> > > behind
> > > > > the
> > > > > > oldest backup, total 3 fulls, should be 2), the job fails, but
the
> > > > > MaitPlan
> > > > > > doesn't and there is no record of a delete or any attempt to
> delete
> > or
> > > > an
> > > > > > error. My guess is that if a delete does not happen for any
> reason,
> > a
> > > > > > MaintPlan history entry for the delete step is not made.
> > > > > >
> > > > > > Any ideas..............
> > > > > >
> > > > > > Frankm
> > > > > > You know you are in trouble when a
> > > > > > learning curve becomes a death spiral
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||We realize most people aren't aware of the situations here in the newsgroups
until they have been around for a while so I just wanted to bring that to
light for everyone's knowledge. Keep up the good questions<g>
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:ekwyz1aXDHA.1748@.TK2MSFTNGP12.phx.gbl...
> I didn't mean to slight anyone. I was unaware of the status and
> affiliation of people responding to questions.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eWMgYZTXDHA.2328@.TK2MSFTNGP12.phx.gbl...
> > I am not sure if you were thinking of the MVP's as being part of the
> > Microsoft respondents but did want to make sure people understand we are
> not
> > actually employees of MS. We are all just volunteers that have been
> > acknowledged by MS in the MVP program. In either case I also want to
> point
> > out that there are lots of good answers from people other than MS or
MVP's
> > that shouldn't go unrecognized either<g>.
> >
> > Thanks
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > > Yep, In previous jobs I did my own scripting, but in this job they use
> > > MaintPlans almost
> > > exclusively. I guess it's because all you have to do is clickity,
> > clickity,
> > > click.... and voila!
> > >
> > > Thanks, I now know that the lack of a error message is a feature, and
> > adjust
> > > accordingly..
> > >
> > > *****************************************
> > > To Microsoft respondents on these newsgroups... Your help is GREATLY
> > > appreciated and
> > > worth every penny. It really helps to be able to ask questions, get
> > answers
> > > and get on with my job.
> > > Thanks to all....................................
> > > *****************************************
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > > > The lack of traceability with the maintenance plan is one of the
many
> > > > reasons I never recommend using it. If you created your own
> scheduled
> > > jobs
> > > > to do those tasks you would have a much better grasp on the
situation
> > when
> > > > things go wrong.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > > SQL Server service and SQL Server Agent both run under the same
> domain
> > > > login
> > > > > that has local admin rights on the machine. There are no problems
> > > logging
> > > > in
> > > > > with that login and doing anything, we do it everyday. The job
owner
> > is
> > > > also
> > > > > that account.
> > > > > This does not happen all the time, only occasionally. The fact
that
> > the
> > > > > DELETE FILES fails is one part. There is also the problem with the
> > fact
> > > > that
> > > > > there is a job failure with a message pointing to the
sqlmaint.exe,
> > but
> > > > > there is absolutely no indication of any failure at the
Maintenance
> > > Plan.
> > > > >
> > > > > Good point about another process holding the file(s). I will check
> to
> > > see
> > > > if
> > > > > another process has the file, but I am unclear as to why the
> > Maintenance
> > > > > Plan does not show an error.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > > > Make sure you don't have any of the Expire or Retain Dates set
but
> > > here
> > > > is
> > > > > > a pretty good overview of why this usually happens by Bill from
> MS:
> > > > > >
> > > > > >
> > > > > > -- Log files don't delete --
> > > > > >
> > > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > > > >
> > > > > > This is likely to be either a permissions problem or a sharing
> > > violation
> > > > > > problem. The maintenance plan is run as a job, and jobs are run
by
> > the
> > > > > > SQLServerAgent service.
> > > > > >
> > > > > > Permissions:
> > > > > > 1. Determine the startup account for the SQLServerAgent service
> > > > > > (Start|Programs|Administrative
> > tools|Services|SQLServerAgent|Startup).
> > > > > This
> > > > > > account is the security context for jobs, and thus the
maintenance
> > > plan.
> > > > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to
a
> > > > domain
> > > > > > account) then skip step 3.
> > > > > > 3. On that box, log onto NT as that account. Using Explorer,
> attempt
> > > to
> > > > > > delete an expired backup. If that succeeds then go to Sharing
> > > Violation
> > > > > > section.
> > > > > > 4. Log onto NT with an account that is an administrator and use
> > > Explorer
> > > > > to
> > > > > > look at the Properties|Security of the folder (where the backups
> > > reside)
> > > > > > and ensure the SQLServerAgent startup account has Full Control.
If
> > the
> > > > > > SQLServerAgent startup account is LocalSystem, then the account
to
> > > > > consider
> > > > > > is SYSTEM.
> > > > > > 5. In NT, if an account is a member of an NT group, and if that
> > group
> > > > has
> > > > > > Access is Denied, then that account will have Access is Denied,
> even
> > > if
> > > > > > that account is also a member of the Administrators group. Thus
> you
> > > may
> > > > > > need to check group permissions (if the Startup Account is a
> member
> > of
> > > a
> > > > > > group).
> > > > > > 6. Keep in mind that permissions (by default) are inherited from
a
> > > > parent
> > > > > > folder. Thus, if the backups are stored in C:\bak, and if
someone
> > had
> > > > > > denied permission to the SQLServerAgent startup account for C:\,
> > then
> > > > > > C:\bak will inherit access is denied.
> > > > > >
> > > > > > Sharing violation:
> > > > > > This is likely to be rooted in a timing issue, with the most
> likely
> > > > cause
> > > > > > being another scheduled process (such as NT Backup or Anti-Virus
> > > > software)
> > > > > > having the backup file open at the time when the SQLServerAgent
> > (i.e.,
> > > > the
> > > > > > maintenance plan job) tried to delete it.
> > > > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > > > 2. I am not sure whether filemon can be scheduled, or you might
be
> > > able
> > > > to
> > > > > > use NT scheduling services to start filemon just before the
> > > maintenance
> > > > > > plan job is started, but the filemon log can become very large,
so
> > it
> > > > > would
> > > > > > be best to start it some short time before the maintenance plan
> > > starts.
> > > > > > 3. Inspect the filemon log for another process that has that
> backup
> > > file
> > > > > > open (if your lucky enough to have started filemon before this
> other
> > > > > > process grabs the backup folder), and inspect the log for the
> > results
> > > > when
> > > > > > the SQLServerAgent agent attempts to open that same file.
> > > > > > 4. Schedule the job or that other process to do their work at
> > > different
> > > > > > times.
> > > > > > 5. You can use the handle utility if you are around at the time
> when
> > > the
> > > > > > job is scheduled to run.
> > > > > >
> > > > > > If the backup files are going to a \\share or a mapped drive (as
> > > opposed
> > > > > to
> > > > > > local drive), then you will need to modify the above (with
respect
> > to
> > > > > where
> > > > > > the tests and utilities are run).
> > > > > >
> > > > > > Finally, inspection of the maintenance plan's history report
might
> > be
> > > > > > useful.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Bill Hollinshead
> > > > > > Microsoft, SQL Server
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Andrew J. Kelly
> > > > > > SQL Server MVP
> > > > > >
> > > > > >
> > > > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > > > We have been experiencing a "problem" with errors running
> > > Maintenance
> > > > > Plan
> > > > > > > Jobs.
> > > > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > > > Below is the code for the Full Backup Job
> step...............
> > > > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > > > DISK -BkUpDB
> > > > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > > > >
> > > > > > > We are deleting backups over 36 hours old because we cannot
> trust
> > > the
> > > > > end
> > > > > > > time of the previous backup. The SQLServer Agent Job shows a
> > failure
> > > > for
> > > > > > the
> > > > > > > MaintPlan (sqlmaint.exe) but there is no failure in the
> MaintPlan.
> > I
> > > > > have
> > > > > > > possibly traced it back to the "DELETE FILES" step of the
> > MaintPlan.
> > > > > This
> > > > > > > appears to fail with no error in the MaintPlan History.
> > > > > > >
> > > > > > > The free drivespace is tight, there is slightly more room
(~1gb)
> > > than
> > > > 3
> > > > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > > > MaintPlan
> > > > > > > needs at least enough room to create the new backup before it
> can
> > > > delete
> > > > > > the
> > > > > > > oldest backup. The Backup succeeds, but the delete fails
(leaves
> > > > behind
> > > > > > the
> > > > > > > oldest backup, total 3 fulls, should be 2), the job fails, but
> the
> > > > > > MaitPlan
> > > > > > > doesn't and there is no record of a delete or any attempt to
> > delete
> > > or
> > > > > an
> > > > > > > error. My guess is that if a delete does not happen for any
> > reason,
> > > a
> > > > > > > MaintPlan history entry for the delete step is not made.
> > > > > > >
> > > > > > > Any ideas..............
> > > > > > >
> > > > > > > Frankm
> > > > > > > You know you are in trouble when a
> > > > > > > learning curve becomes a death spiral
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Frank,
The idea about maintenance plans a great idea for a future product
enhancement. I'd recommend that you forward the recommendation to the
Microsoft Wish Program:
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed later in this article.
Let us know how we can improve our products.
Product Enhancement suggestions can include:
Improvements on existing products.
Suggestions for additional features.
Ways to make products easier to use.
All product enhancement suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
In Internet Explorer 6, click Send Feedback on the Help menu and then click
the link in the Product Suggestion section of the page that appears.
In Windows XP, click Help and Support on the Start menu. Click Send your
feedback to Microsoft, and then fill out the Product Suggestion page that
appears.
Visit the following Microsoft Web site:
http://www.microsoft.com/ms.htm
Click Microsoft.com Guide in the upper-right corner of the page and then
click Contact Us . Click the link in the Product Suggestion section of the
page that appears.
Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.
E-mail - To send comments or suggestions via e-mail, use the following
Microsoft Wish Program e-mail address, mswish@.microsoft.com.
FAX - To send comments or suggestions via FAX, use the following Microsoft
FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.
US Mail - To send comments or suggestions via US Mail, use the following
Microsoft mailing address:
Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team,
classified for easy access, and routed to the product or service team to
drive Microsoft product and/or service improvements. Because we receive an
abundance of suggestions (over 69,000 suggestions a year!) we can't
guarantee that each request makes it into a final product or service. But
we can tell you that each suggestion has been received and is being
reviewed by the team that is most capable of addressing it.
All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
Sincerely,
Alick Ye, MCSD
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
| From: "frankm" <frank@.nxspxm.mallardcentral.com>
| References: <#NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl> <eKPZ7PHXDHA.656
|
| SQL Server service and SQL Server Agent both run under the same domain
login
| that has local admin rights on the machine. There are no problems logging
in
| with that login and doing anything, we do it everyday. The job owner is
also
| that account.
| This does not happen all the time, only occasionally. The fact that the
| DELETE FILES fails is one part. There is also the problem with the fact
that
| there is a job failure with a message pointing to the sqlmaint.exe, but
| there is absolutely no indication of any failure at the Maintenance Plan.
|
| Good point about another process holding the file(s). I will check to see
if
| another process has the file, but I am unclear as to why the Maintenance
| Plan does not show an error.
|
|
|
|
|
| "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
| > Make sure you don't have any of the Expire or Retain Dates set but here
is
| > a pretty good overview of why this usually happens by Bill from MS:
| >
| >
| > -- Log files don't delete --
| >
| > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
| >
| > This is likely to be either a permissions problem or a sharing violation
| > problem. The maintenance plan is run as a job, and jobs are run by the
| > SQLServerAgent service.
| >
| > Permissions:
| > 1. Determine the startup account for the SQLServerAgent service
| > (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
| This
| > account is the security context for jobs, and thus the maintenance plan.
| > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
domain
| > account) then skip step 3.
| > 3. On that box, log onto NT as that account. Using Explorer, attempt to
| > delete an expired backup. If that succeeds then go to Sharing Violation
| > section.
| > 4. Log onto NT with an account that is an administrator and use Explorer
| to
| > look at the Properties|Security of the folder (where the backups reside)
| > and ensure the SQLServerAgent startup account has Full Control. If the
| > SQLServerAgent startup account is LocalSystem, then the account to
| consider
| > is SYSTEM.
| > 5. In NT, if an account is a member of an NT group, and if that group
has
| > Access is Denied, then that account will have Access is Denied, even if
| > that account is also a member of the Administrators group. Thus you may
| > need to check group permissions (if the Startup Account is a member of a
| > group).
| > 6. Keep in mind that permissions (by default) are inherited from a
parent
| > folder. Thus, if the backups are stored in C:\bak, and if someone had
| > denied permission to the SQLServerAgent startup account for C:\, then
| > C:\bak will inherit access is denied.
| >
| > Sharing violation:
| > This is likely to be rooted in a timing issue, with the most likely
cause
| > being another scheduled process (such as NT Backup or Anti-Virus
software)
| > having the backup file open at the time when the SQLServerAgent (i.e.,
the
| > maintenance plan job) tried to delete it.
| > 1. Download filemon and handle from www.sysinternals.com.
| > 2. I am not sure whether filemon can be scheduled, or you might be able
to
| > use NT scheduling services to start filemon just before the maintenance
| > plan job is started, but the filemon log can become very large, so it
| would
| > be best to start it some short time before the maintenance plan starts.
| > 3. Inspect the filemon log for another process that has that backup file
| > open (if your lucky enough to have started filemon before this other
| > process grabs the backup folder), and inspect the log for the results
when
| > the SQLServerAgent agent attempts to open that same file.
| > 4. Schedule the job or that other process to do their work at different
| > times.
| > 5. You can use the handle utility if you are around at the time when the
| > job is scheduled to run.
| >
| > If the backup files are going to a \\share or a mapped drive (as opposed
| to
| > local drive), then you will need to modify the above (with respect to
| where
| > the tests and utilities are run).
| >
| > Finally, inspection of the maintenance plan's history report might be
| > useful.
| >
| > Thanks,
| >
| > Bill Hollinshead
| > Microsoft, SQL Server
| >
| > --
| >
| > Andrew J. Kelly
| > SQL Server MVP
| >
| >
| > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
| > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
| > > We have been experiencing a "problem" with errors running Maintenance
| Plan
| > > Jobs.
| > > This is a Transaction Log and Full Backup Maintenance Plan.
| > > Below is the code for the Full Backup Job step...............
| > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
| > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
| > DISK -BkUpDB
| > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
| > >
| > > We are deleting backups over 36 hours old because we cannot trust the
| end
| > > time of the previous backup. The SQLServer Agent Job shows a failure
for
| > the
| > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
| have
| > > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
| This
| > > appears to fail with no error in the MaintPlan History.
| > >
| > > The free drivespace is tight, there is slightly more room (~1gb) than
3
| > > fulls of the largest backup(~20gb each) (we are saving 2). The
MaintPlan
| > > needs at least enough room to create the new backup before it can
delete
| > the
| > > oldest backup. The Backup succeeds, but the delete fails (leaves
behind
| > the
| > > oldest backup, total 3 fulls, should be 2), the job fails, but the
| > MaitPlan
| > > doesn't and there is no record of a delete or any attempt to delete or
| an
| > > error. My guess is that if a delete does not happen for any reason, a
| > > MaintPlan history entry for the delete step is not made.
| > >
| > > Any ideas..............
| > >
| > > Frankm
| > > You know you are in trouble when a
| > > learning curve becomes a death spiral
| > >
| > >
| >
| >
|
|
|

Maintenance Plans and corresponding Jobs

SQL Server 2005: Creating a maintenance plan generates a job. Two
questions:
1. If I change the job instead of the maintenance plan, will the maintenance
plan break? And/or is there a way to get the job and maintenance plan out
of sycnh?
2. SQL Server 2000 generaetd cryptic command lines when creating maintenance
plans. Worse yet, error messages were nasty and tough to debug. Has this
been improved in 2005?
Thanks,
MarkMark,
In some ways it is better. If you make significant changes to the job and
then go and make a change in the maintenance plan some of those job changes
will be deleted.
If you add extra steps then these will all disappear. It would also depend
on the build that created the maintenance plan/job and the build that you
change them with if they were created before SP2 post builds.
Chris
"Mark" <mark@.idonotlikespam.com> wrote in message
news:uGjNQ4jnIHA.1204@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005: Creating a maintenance plan generates a job. Two
> questions:
> 1. If I change the job instead of the maintenance plan, will the
> maintenance plan break? And/or is there a way to get the job and
> maintenance plan out of sycnh?
> 2. SQL Server 2000 generaetd cryptic command lines when creating
> maintenance plans. Worse yet, error messages were nasty and tough to
> debug. Has this been improved in 2005?
> Thanks,
> Mark
>

Maintenance Plans and corresponding Jobs

SQL Server 2005: Creating a maintenance plan generates a job. Two
questions:
1. If I change the job instead of the maintenance plan, will the maintenance
plan break? And/or is there a way to get the job and maintenance plan out
of sycnh?
2. SQL Server 2000 generaetd cryptic command lines when creating maintenance
plans. Worse yet, error messages were nasty and tough to debug. Has this
been improved in 2005?
Thanks,
Mark
Mark,
In some ways it is better. If you make significant changes to the job and
then go and make a change in the maintenance plan some of those job changes
will be deleted.
If you add extra steps then these will all disappear. It would also depend
on the build that created the maintenance plan/job and the build that you
change them with if they were created before SP2 post builds.
Chris
"Mark" <mark@.idonotlikespam.com> wrote in message
news:uGjNQ4jnIHA.1204@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2005: Creating a maintenance plan generates a job. Two
> questions:
> 1. If I change the job instead of the maintenance plan, will the
> maintenance plan break? And/or is there a way to get the job and
> maintenance plan out of sycnh?
> 2. SQL Server 2000 generaetd cryptic command lines when creating
> maintenance plans. Worse yet, error messages were nasty and tough to
> debug. Has this been improved in 2005?
> Thanks,
> Mark
>