manage Data replicated

I have two Db with Merge Transactional replication.
I would like to delete some data in Db replicated but maintain in the
principal DB.
what i can do, what tunig for this feature ?
thanks in advance
stper
maybe one solution is to create NOT a Merge replication but a Transactinal
replication ?
thanks
stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:#mhABKBlEHA.3544@.TK2MSFTNGP15.phx.gbl...
> I have two Db with Merge Transactional replication.
> I would like to delete some data in Db replicated but maintain in the
> principal DB.
> what i can do, what tunig for this feature ?
> thanks in advance
> stper
>

manage Data replicated

I have two Db with Merge Transactional replication.
I would like to delete some data in Db replicated but maintain in the
principal DB.
what i can do, what tunig for this feature ?
thanks in advance
stpermaybe one solution is to create NOT a Merge replication but a Transactinal
replication ?
thanks
stper
"stper" <stefano.perabo@.speedautomazione.it> ha scritto nel messaggio
news:#mhABKBlEHA.3544@.TK2MSFTNGP15.phx.gbl...
> I have two Db with Merge Transactional replication.
> I would like to delete some data in Db replicated but maintain in the
> principal DB.
> what i can do, what tunig for this feature ?
> thanks in advance
> stper
>sql

Manage Data

Hello,
I am working on an ASP.NET web site using an SQL database.
Is there a way to Insert/Delete and Update data in the database
without creating all the forms in ASP.NET?
Since I am the only person to work with the database it would be
easier for me than creating all ASP.NET forms.
Thanks,
MiguelHi
Hmm, so get in SSMS and/or Query Builder and issue DML there.
"shapper" <mdmoura@.gmail.com> wrote in message
news:d128e2f4-c3fc-4873-b491-1ccc1ee40928@.t54g2000hsg.googlegroups.com...
> Hello,
> I am working on an ASP.NET web site using an SQL database.
> Is there a way to Insert/Delete and Update data in the database
> without creating all the forms in ASP.NET?
> Since I am the only person to work with the database it would be
> easier for me than creating all ASP.NET forms.
> Thanks,
> Miguel

Manage Data

Hello,
I am working on an ASP.NET web site using an SQL database.
Is there a way to Insert/Delete and Update data in the database
without creating all the forms in ASP.NET?
Since I am the only person to work with the database it would be
easier for me than creating all ASP.NET forms.
Thanks,
Miguel
Hi
Hmm, so get in SSMS and/or Query Builder and issue DML there.
"shapper" <mdmoura@.gmail.com> wrote in message
news:d128e2f4-c3fc-4873-b491-1ccc1ee40928@.t54g2000hsg.googlegroups.com...
> Hello,
> I am working on an ASP.NET web site using an SQL database.
> Is there a way to Insert/Delete and Update data in the database
> without creating all the forms in ASP.NET?
> Since I am the only person to work with the database it would be
> easier for me than creating all ASP.NET forms.
> Thanks,
> Miguel

Making a row delete itself at a certain time?

Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?

Also, I have another question:
when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:

PK----Item----price
1----XYZ1----$$$$
2----XYZ2----$$$$
3----XYZ3----$$$$

when I delete PK 2 this is what happens

PK----Item----price
1----XYZ1----$$$$
3----XYZ3----$$$$

the PKs are not in consecutive order

What I want is this to happen: when PK 2 is deleted I need it to show as follows

PK----Item----price
1----XYZ1----$$$$
2----XYZ3----$$$$

The PKs stay in consecutive order.

See how the PK are still in consecutive order?I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application.Why delete it at all? Save the expiration date within your row, and then create a view on that table with something like WHERE expiration_date <= current_date. Then make your application use the view to retrieve the data (or simply add the condition to your application logic), thus it will never see expired rows. If you put an index on that column the retrieval should be quite fast.

If you are concerned about space, than run a batch job every night that deletes the expired rows.|||you could create an agent job that would delete the rows also.

make a stored procedure

Hi

In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this

Alvin

Hi,

Here is a possible solution:

delete from tableName
where datettimeField < dateadd(hour, -1, getdate())

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

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

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

Maintenance plans not deleting old transaction log backups

I'm the dba for a large company and have setup many maintenance plans for our sql servers. One of the options I have set is to delete old backups of databases and transaction logs after 2 days. For some reason, the old database backups are being deleted, but the old transaction logs backups are not. I have them being put in the same directory. Anybody have any ideas why this is happening and what if any fix is available?

Those are separate items that you check off in the options for each step - are you sure you asked the wizard to do that? If possible, drop the old plans and recreate them, checking that section to make sure you select that option.

Other than missing the check box, I don't know what else might cause this. Both delete operations use the same delete stored procedure, so if one works, the other one should.

If you do change your plans and this happens again, check the event logs and the SQL Server error logs.

Maintenance Plans Cleanup Task does not delete files

Hello,

I've created a Maintenance Plan with a Backup Task and a Cleanup Task. The Backup task works fine, but not the cleanup task.

When I execute the maintenant plan, there's none error message but the old backup files aren't deleted.

Could you help me please?

Thanks

I was a bit puzzled by this as well, but then i read the help file:

Backup and restore history

Retaining records of when recent backups were created can help SQL Server create a recovery plan when you want to restore a database. The retention period should be at least the frequency of full database back ups.

SQL Server Agent Job history

This history can help you troubleshoot failed jobs, or determine why database actions occurred.

Maintenance plan history

This history can help you troubleshoot failed maintenance plan jobs, or determine why database actions occurred.

So it actually makes a lot of sence that the back-up itself is not deleted, only the information in the database that says the back-up was made.

|||

You may be running into the below issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=336728&SiteID=1

Backup task supports subfolder but cleanup task did not in RTM. This is addressed in SP1.

Let me know if this is not the issue you are running into.

Gops Dwarak

|||

Oops,

Maintenance Clean-up Task != History Clean-up Task

My bad....

Maintenance Plans and Delete files

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

Maintenance Plans

When you setup a maintenance plan to back up a database and you give it the
option to delete a database older than 1 day. Does it delete the old backup
before it creates the new backup?
After. There's no setting for this, so you cannot change this behavior. Maint plans are totally
reworked in 2005, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Patricia> wrote in message news:OVpW%23yfFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> When you setup a maintenance plan to back up a database and you give it the option to delete a
> database older than 1 day. Does it delete the old backup before it creates the new backup?
>
>
|||The clean up routines will run last unless you have edited the job and
changed the order.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<Patricia> wrote in message news:OVpW%23yfFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> When you setup a maintenance plan to back up a database and you give it
> the option to delete a database older than 1 day. Does it delete the old
> backup before it creates the new backup?
>
>

Maintenance Plans

When you setup a maintenance plan to back up a database and you give it the
option to delete a database older than 1 day. Does it delete the old backup
before it creates the new backup?After. There's no setting for this, so you cannot change this behavior. Maint plans are totally
reworked in 2005, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Patricia> wrote in message news:OVpW%23yfFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> When you setup a maintenance plan to back up a database and you give it the option to delete a
> database older than 1 day. Does it delete the old backup before it creates the new backup?
>
>|||The clean up routines will run last unless you have edited the job and
changed the order.
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<Patricia> wrote in message news:OVpW%23yfFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> When you setup a maintenance plan to back up a database and you give it
> the option to delete a database older than 1 day. Does it delete the old
> backup before it creates the new backup?
>
>

Maintenance Plans

When you setup a maintenance plan to back up a database and you give it the
option to delete a database older than 1 day. Does it delete the old backup
before it creates the new backup?After. There's no setting for this, so you cannot change this behavior. Main
t plans are totally
reworked in 2005, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<Patricia> wrote in message news:OVpW%23yfFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> When you setup a maintenance plan to back up a database and you give it th
e option to delete a
> database older than 1 day. Does it delete the old backup before it create
s the new backup?
>
>|||The clean up routines will run last unless you have edited the job and
changed the order.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<Patricia> wrote in message news:OVpW%23yfFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> When you setup a maintenance plan to back up a database and you give it
> the option to delete a database older than 1 day. Does it delete the old
> backup before it creates the new backup?
>
>

Maintenance Plan: Delete old files not working right

We do a full backup every night. For some reason the backup files are
staying on the server for 5 full days. Since each backup is 6GB this
is presenting a disk space problem. As far as we can tell, we've
changed all of our SQLServer Maintenance backup jobs to delete any
files older than 23 hours, and even rebooted the server to ensure any
old settings that were set for 5 day file deletion are gone.
Does anyone know why the server is continuing to store files as old as
5 days when we've set our systems up to delete after 23 hours? What
are we missing?
Thanks,
-Rob
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RW" <rwuhrman@.hotmail.com> wrote in message news:a628b717.0410270951.39dc39f3@.posting.google.c om...
> We do a full backup every night. For some reason the backup files are
> staying on the server for 5 full days. Since each backup is 6GB this
> is presenting a disk space problem. As far as we can tell, we've
> changed all of our SQLServer Maintenance backup jobs to delete any
> files older than 23 hours, and even rebooted the server to ensure any
> old settings that were set for 5 day file deletion are gone.
> Does anyone know why the server is continuing to store files as old as
> 5 days when we've set our systems up to delete after 23 hours? What
> are we missing?
> Thanks,
> -Rob

Maintenance Plan: Delete old files not working right

We do a full backup every night. For some reason the backup files are
staying on the server for 5 full days. Since each backup is 6GB this
is presenting a disk space problem. As far as we can tell, we've
changed all of our SQLServer Maintenance backup jobs to delete any
files older than 23 hours, and even rebooted the server to ensure any
old settings that were set for 5 day file deletion are gone.
Does anyone know why the server is continuing to store files as old as
5 days when we've set our systems up to delete after 23 hours? What
are we missing?
Thanks,
-RobBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RW" <rwuhrman@.hotmail.com> wrote in message news:a628b717.0410270951.39dc39f3@.posting.google.com...
> We do a full backup every night. For some reason the backup files are
> staying on the server for 5 full days. Since each backup is 6GB this
> is presenting a disk space problem. As far as we can tell, we've
> changed all of our SQLServer Maintenance backup jobs to delete any
> files older than 23 hours, and even rebooted the server to ensure any
> old settings that were set for 5 day file deletion are gone.
> Does anyone know why the server is continuing to store files as old as
> 5 days when we've set our systems up to delete after 23 hours? What
> are we missing?
> Thanks,
> -Rob

Maintenance Plan: Delete old files not working right

We do a full backup every night. For some reason the backup files are
staying on the server for 5 full days. Since each backup is 6GB this
is presenting a disk space problem. As far as we can tell, we've
changed all of our SQLServer Maintenance backup jobs to delete any
files older than 23 hours, and even rebooted the server to ensure any
old settings that were set for 5 day file deletion are gone.
Does anyone know why the server is continuing to store files as old as
5 days when we've set our systems up to delete after 23 hours? What
are we missing?
Thanks,
-RobBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RW" <rwuhrman@.hotmail.com> wrote in message news:a628b717.0410270951.39dc39f3@.posting.googl
e.com...
> We do a full backup every night. For some reason the backup files are
> staying on the server for 5 full days. Since each backup is 6GB this
> is presenting a disk space problem. As far as we can tell, we've
> changed all of our SQLServer Maintenance backup jobs to delete any
> files older than 23 hours, and even rebooted the server to ensure any
> old settings that were set for 5 day file deletion are gone.
> Does anyone know why the server is continuing to store files as old as
> 5 days when we've set our systems up to delete after 23 hours? What
> are we missing?
> Thanks,
> -Rob

Maintenance Plan won't run, delete, or modify

Had to replace leased server hardware running Server2003, Sql2005. Had
machine Server B, set up sql2005, set up maintenance plan to do full backup
on all user dbs every night to Server C network share. Worked great for over
a week. Came time to do the swamp, brought down old server, renamed Server B
to Server A, ran:
EXEC sp_dropserver '<old_name>'
GO
EXEC sp_addserver '<new_name>', 'local'
GO
All other functions to the database seem to be working fine. Was using a
network administrator account - same one that worked before the name change
so the SID is the same. Unfortunately, I decided to try and delete and start
over in case it was just a gliche. Now, the history seems to be gone, but I
can't actually delete the job or the maintenance plan. Get an error "Does
not allow remote connections." even when I'm physically sitting at the
machine.
Now, maintenance plans won't run - can't delete them, can't modify them.
Other dts packages that execute a query or bring in data from other
datasources work just fine.
Help!?Have a litte more information. I successfully recreated the two maintenance
plans and with one minor problem, they work fine. I'll deal with the new
ones later.
But, since I've pretty much got new plans for system and user databases, I'd
like to delete the originals, but can't. For the systemMtnc, when I try to
delete, I get this error when I'm physically sitting at the machine:
"Exception has been thrown by the target of an invocation(mscorlib). An
error has occurred while establishing a connection to the server. When
connecting to sql server 2005, this failure may be cause by the fact that
under the default settings, sql server does not allow remote connecitons.
(provider: tcp provider, error: 0 no such host is know) error 11001
I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
won't let me. Any ideas?
"Janet" wrote:
> Had to replace leased server hardware running Server2003, Sql2005. Had
> machine Server B, set up sql2005, set up maintenance plan to do full backup
> on all user dbs every night to Server C network share. Worked great for over
> a week. Came time to do the swamp, brought down old server, renamed Server B
> to Server A, ran:
> EXEC sp_dropserver '<old_name>'
> GO
> EXEC sp_addserver '<new_name>', 'local'
> GO
> All other functions to the database seem to be working fine. Was using a
> network administrator account - same one that worked before the name change
> so the SID is the same. Unfortunately, I decided to try and delete and start
> over in case it was just a gliche. Now, the history seems to be gone, but I
> can't actually delete the job or the maintenance plan. Get an error "Does
> not allow remote connections." even when I'm physically sitting at the
> machine.
> Now, maintenance plans won't run - can't delete them, can't modify them.
> Other dts packages that execute a query or bring in data from other
> datasources work just fine.
> Help!?|||Found this and it worked like a charm - hope it helps someone else.
1. Select the ID with the select statement
select * from sysmaintplan_plans
2. Replace with the selected ID and run the delete statements
delete from sysmaintplan_log where plan_id = ''
delete from sysmaintplan_subplans where plan_id = ''
delete from sysmaintplan_plans where id = ''
3. Delete the SQL Server Jobs with the Management Studio
"Janet" wrote:
> Have a litte more information. I successfully recreated the two maintenance
> plans and with one minor problem, they work fine. I'll deal with the new
> ones later.
> But, since I've pretty much got new plans for system and user databases, I'd
> like to delete the originals, but can't. For the systemMtnc, when I try to
> delete, I get this error when I'm physically sitting at the machine:
> "Exception has been thrown by the target of an invocation(mscorlib). An
> error has occurred while establishing a connection to the server. When
> connecting to sql server 2005, this failure may be cause by the fact that
> under the default settings, sql server does not allow remote connecitons.
> (provider: tcp provider, error: 0 no such host is know) error 11001
> I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
> won't let me. Any ideas?
>
> "Janet" wrote:
> > Had to replace leased server hardware running Server2003, Sql2005. Had
> > machine Server B, set up sql2005, set up maintenance plan to do full backup
> > on all user dbs every night to Server C network share. Worked great for over
> > a week. Came time to do the swamp, brought down old server, renamed Server B
> > to Server A, ran:
> > EXEC sp_dropserver '<old_name>'
> > GO
> > EXEC sp_addserver '<new_name>', 'local'
> > GO
> >
> > All other functions to the database seem to be working fine. Was using a
> > network administrator account - same one that worked before the name change
> > so the SID is the same. Unfortunately, I decided to try and delete and start
> > over in case it was just a gliche. Now, the history seems to be gone, but I
> > can't actually delete the job or the maintenance plan. Get an error "Does
> > not allow remote connections." even when I'm physically sitting at the
> > machine.
> >
> > Now, maintenance plans won't run - can't delete them, can't modify them.
> > Other dts packages that execute a query or bring in data from other
> > datasources work just fine.
> >
> > Help!?