Hi all,
Im currently investigating a SQL Server 2005 DB maintenence plan for my
company's software product.
I'm still learning this stuff so if I'd appreciate any comments on my
questions below :)
1) Assume the DB is using a FULL recovery model.
When I do a full backup of the database, will the transaction log be
truncated?
Or will it only be truncated when I perform a transaction log backup?
2) How often should transaction log backups be run? I'm thinking every
couple of hours... probably depends on the performance though (might
make it more frequent)
What is the performance hit when running a transaction log?
3) We currently do an index rebuild nightly. Would it be worth doing
this before a full backup (I assume that the rebuild would contribute
to the transaction log, so if the full backup clears these out then it
would be worth doing it before the backup? Or are there risks involved
with the rebuild so it is better practice to do it after the full
backup).
For one site, the db needs to be up 24/7.
I was thinking about running index rebuild in online mode for this, but
would this only work on Microsoft SQL Server 2005 Enterprise Edition?
BOL under "Guidelines for Performing Online Index Operations" says that
"Online index operations are available only in Microsoft SQL Server
2005 Enterprise Edition." so I'm thinking that this might not work'
4) We do not use auto update statistics, so run updates statistics
every night.
Since we "rebuild the indexes" in the maintenance plan prior to running
the update stats, is the update stats step necessary? I think I read
somewhere that rebuilding the indexes updates the statistics
automatically...
If we use INDEXDEFRAG instead, then we would need to run update stats
yeah? and if so, would it be best to run it before running indexdefrag
or after?
4) Just for your information, I am not going to shrink the log as from
what I've read this is not good for performance as the log file will no
doubt increase in size again (and it encourages fragmentation). But I'm
hoping that the combination of regular transaction log backups will
keep the log file at a consistent size.
I know there are a few questions in there, any comments would be really
appreciated!
Cheers!
Daviddavconts@.gmail.com wrote:
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
No, a full backup will not truncate the log file
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
The frequency depends on your tolerance for data loss. If you can
afford to lose 2 hours of work, then backup the log every 2 hours. If
you can only afford to lose 5 minutes of work, backup the log every 5
minutes.
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Consider only rebuilding those indexes that are badly fragmented. See
http://www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
You could use DBCC INDEXDEFRAG, or @.Mode = 'DEFRAG' if running the
script I linked to above.
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
I assume you have auto-stats turned off due to potential performance
problems? It's very rare to encounter such problems, I've never had
cause to turn off auto stats, even on OLTP databases approaching 200GB.
I'd turn them back on.
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
Correct, DBCC INDEXDEFRAG does not update stats. You'll need to
manually update them after defragging, or turn auto-stats on.
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Do not shrink the log file. Size it properly, do a thorough
defragmentation of the OS volume, you'll enjoy optimal performance.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||1) Only when a TLog backup is run. This topic is much more in depth than
meets the eye, make sure you read all about it in BOL before implementing in
production.
2) I don't think there is usually much performance hit. How often you want
to do it depends on how much data loss is acceptable.
3) IMO it's never a bad idea to have a good backup before doing stuff like
this. You may want to write TSQL code to switch the recovery model from Full
to Bulk Logged before doing your rebuilds. It will make it faster and your
TLogs wont grow as fast. Again, read BOL thoroughly before doing this. Every
night seems a bit excessive too me, but if thats what you need to do
depending on Inserts/ Deletes....
Looks like it would work on EE to me, but I'll let others comment.
4) It shouldn't be necessary.
I'll usually do update stats after a defrag, but I have no idea why?
4) You are correct.
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
NO. Only if you perform BACKUP LOG File
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
It depends on your business requirements . I do it every 15 minutes
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
Just make sure that REBUILD INDEX command will not concur with a BACKUP
statement
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
REBUILD INDEX updates statistics
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
After INDEXDEFRAG
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
Correct. Don't be afraid that the LOG file is huge ( I assume you perform
BACKUP LOG ) , that means SQL Server has enough room to perfom its work
<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks for all the great replies above.. it has really clarified a lot
of the reading i've been doing to get up to speed on this! Really
appreciate it.
Thanks for the link too Tracy, that stored procedure looks interesting,
I'll give it a go.|||In addition to the other posts, check out the Books Online that comes with SQL Server 2005,
sys.dm_db_index_physical_stats. It has code you can use that depending on the fragmentation level
either does nothing, REBUILD or REORGANIZE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> Im currently investigating a SQL Server 2005 DB maintenence plan for my
> company's software product.
> I'm still learning this stuff so if I'd appreciate any comments on my
> questions below :)
> 1) Assume the DB is using a FULL recovery model.
> When I do a full backup of the database, will the transaction log be
> truncated?
> Or will it only be truncated when I perform a transaction log backup?
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
> 3) We currently do an index rebuild nightly. Would it be worth doing
> this before a full backup (I assume that the rebuild would contribute
> to the transaction log, so if the full backup clears these out then it
> would be worth doing it before the backup? Or are there risks involved
> with the rebuild so it is better practice to do it after the full
> backup).
> For one site, the db needs to be up 24/7.
> I was thinking about running index rebuild in online mode for this, but
> would this only work on Microsoft SQL Server 2005 Enterprise Edition?
> BOL under "Guidelines for Performing Online Index Operations" says that
> "Online index operations are available only in Microsoft SQL Server
> 2005 Enterprise Edition." so I'm thinking that this might not work'
> 4) We do not use auto update statistics, so run updates statistics
> every night.
> Since we "rebuild the indexes" in the maintenance plan prior to running
> the update stats, is the update stats step necessary? I think I read
> somewhere that rebuilding the indexes updates the statistics
> automatically...
> If we use INDEXDEFRAG instead, then we would need to run update stats
> yeah? and if so, would it be best to run it before running indexdefrag
> or after?
> 4) Just for your information, I am not going to shrink the log as from
> what I've read this is not good for performance as the log file will no
> doubt increase in size again (and it encourages fragmentation). But I'm
> hoping that the combination of regular transaction log backups will
> keep the log file at a consistent size.
> I know there are a few questions in there, any comments would be really
> appreciated!
> Cheers!
> David
>|||Thanks Tibor
I tried that script in BOL as you mention above (with a USE dbname
clause at the top)
but I get an error message:
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '('.
>From what I can see it relates to the following line in the script:
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
'LIMITED')
Not sure why this is failing - this is a straight copy from BOL so am a
bit confused'|||Its ok I got it working...
I added:
DECLARE @.db_id smallint;
SET @.db_id = DB_ID(N'RMHCMS');
at the top and then changed the following from:
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
to:
sys.dm_db_index_physical_stats (@.db_id, NULL, NULL , NULL, 'LIMITED')
Cheers,
David
davconts@.gmail.com wrote:
> Thanks Tibor
> I tried that script in BOL as you mention above (with a USE dbname
> clause at the top)
> but I get an error message:
> Msg 102, Level 15, State 1, Line 24
> Incorrect syntax near '('.
> >From what I can see it relates to the following line in the script:
> FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL,
> 'LIMITED')
> Not sure why this is failing - this is a straight copy from BOL so am a
> bit confused'|||<davconts@.gmail.com> wrote in message
news:1151975288.606525.38030@.h44g2000cwa.googlegroups.com...
> Hi all,
> 2) How often should transaction log backups be run? I'm thinking every
> couple of hours... probably depends on the performance though (might
> make it more frequent)
> What is the performance hit when running a transaction log?
Not really. But in any case, think of it as a trade-off.
If you do it say every 2 hours vs every 15 minutes.
The 2 hour version would have 8 times as much info to backup as the 15
minute one.
So if there WERE a performance issue, it would last 8 times as long.
Generally though plan them according to what your DR model requires.
For example our Dev databases we backup nightly and that's about it. It's
really not worth the trouble to do it more often.
Our production ones we do transaction log backups every 15 minutes and full
backups 3 nights a week.
Saturday, February 25, 2012
Maintenance Plans and Integrity checks
Just inherited a couple os SQL V7 servers and am reviewing the backups they
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQL
If #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQL
If #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL
Maintenance Plans and Integrity checks
Just inherited a couple os SQL V7 servers and am reviewing the backups they
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
--
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQLIf #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
--
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
--
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQLIf #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
--
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL
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
| > >
| > >
| >
| >
|
|
|
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
>
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
>
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
>
Maintenance Plans and cleaning up backups in subfolders...
The maintenance job I have creates sub-folders for each database when it
backups.
The Cleanup Job is pointing to the root of this folder but it's not deleting
previous backups. It's set to delete files older than 2 days.
Does this not supported by the SQL2K5?
Should I not set it to create folders for each database backup?
Thanks in advance...
Do you know where can I enable this option?
"Tibor Karaszi" wrote:
> With sp1, you have an option to recurse sub-folders. also, there has been lots of work on maint
> plans in sp2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
>
|||It is in the cleanup task. You have to open it in the maintenance plan
editor and you will see it.
Andrew J. Kelly SQL MVP
"Artunc" <artunc_u2@.hotmail.com> wrote in message
news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...[vbcol=seagreen]
> Do you know where can I enable this option?
> "Tibor Karaszi" wrote:
|||I don't see it in there, I'm running SP1.
Do I need to install SP2 to see it?
"Andrew J. Kelly" wrote:
> It is in the cleanup task. You have to open it in the maintenance plan
> editor and you will see it.
> --
> Andrew J. Kelly SQL MVP
> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...
>
>
|||Never mind, I see it now.
I had to open the Management Studio localy on the SQL Server.
For some reason, I don't see this option when I look at it remotely from my
workstation.
Thanks everyone...
"Artunc" wrote:
[vbcol=seagreen]
> I don't see it in there, I'm running SP1.
> Do I need to install SP2 to see it?
> "Andrew J. Kelly" wrote:
backups.
The Cleanup Job is pointing to the root of this folder but it's not deleting
previous backups. It's set to delete files older than 2 days.
Does this not supported by the SQL2K5?
Should I not set it to create folders for each database backup?
Thanks in advance...
Do you know where can I enable this option?
"Tibor Karaszi" wrote:
> With sp1, you have an option to recurse sub-folders. also, there has been lots of work on maint
> plans in sp2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> news:60572E3A-8C37-4236-A7DE-7E16EE73292D@.microsoft.com...
>
|||It is in the cleanup task. You have to open it in the maintenance plan
editor and you will see it.
Andrew J. Kelly SQL MVP
"Artunc" <artunc_u2@.hotmail.com> wrote in message
news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...[vbcol=seagreen]
> Do you know where can I enable this option?
> "Tibor Karaszi" wrote:
|||I don't see it in there, I'm running SP1.
Do I need to install SP2 to see it?
"Andrew J. Kelly" wrote:
> It is in the cleanup task. You have to open it in the maintenance plan
> editor and you will see it.
> --
> Andrew J. Kelly SQL MVP
> "Artunc" <artunc_u2@.hotmail.com> wrote in message
> news:201DF41C-9E0C-44A2-9582-332E33E25A09@.microsoft.com...
>
>
|||Never mind, I see it now.
I had to open the Management Studio localy on the SQL Server.
For some reason, I don't see this option when I look at it remotely from my
workstation.
Thanks everyone...
"Artunc" wrote:
[vbcol=seagreen]
> I don't see it in there, I'm running SP1.
> Do I need to install SP2 to see it?
> "Andrew J. Kelly" wrote:
Subscribe to:
Posts (Atom)