Bakup dosen't delete files

When using SQLserver to take backup throw a maintance plan
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill 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.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>

BAK, TRN files

Hey all,
I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
them if you are running server backup IE Using Arcserve.
We seem to be constantly running out of space on your drives so I have been
so far moving the BAK and TRN files to different disks but as you can imagine
this cant continue for too long.
We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
this seems huge to me, as the drive is only 29 Gb.
A friend mentioned that you shouldn't limit the size of the TRN logs as your
only shooting yourself inthe foot should the db go down, but at this Im going
to have to start getting new disks to hold the increasing amounts.
Anyone any ideas of what I can do, is it ok to delete these files or limit
the growth all together, say to a Gb a DB ?
ThanksYes, you have to delete old backup files. Determine a strategy for how long to keep them, and with
this, consider hoe you store these files on tape.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <Adrian@.discussions.microsoft.com> wrote in message
news:03BB4744-B688-4190-9220-17993AEB37BB@.microsoft.com...
> Hey all,
> I know *.BAK and *.TRN files are backups and logs. But it is ok to delete
> them if you are running server backup IE Using Arcserve.
> We seem to be constantly running out of space on your drives so I have been
> so far moving the BAK and TRN files to different disks but as you can imagine
> this cant continue for too long.
> We have 20Gb of data on 1 drive, but the BAK and TRM files are another 10gbs
> this seems huge to me, as the drive is only 29 Gb.
> A friend mentioned that you shouldn't limit the size of the TRN logs as your
> only shooting yourself inthe foot should the db go down, but at this Im going
> to have to start getting new disks to hold the increasing amounts.
> Anyone any ideas of what I can do, is it ok to delete these files or limit
> the growth all together, say to a Gb a DB ?
> Thanks

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned the
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
Robert
"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned the
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
--
Robert"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned th
e
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
--
Robert"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad Logs

Let me correct that. The data part of the database is also corrupt. Is there
a way to delete the corrupted part of the data base and try a partial
recovery?
--
Chris DavoliAs Greg stated you are best to call MS PSS and work directly with someone
there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>|||what is microsoft PSS? Is there a phone or email or something?
--
Chris Davoli
"Andrew J. Kelly" wrote:
> As Greg stated you are best to call MS PSS and work directly with someone
> there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> > Let me correct that. The data part of the database is also corrupt. Is
> > there
> > a way to delete the corrupted part of the data base and try a partial
> > recovery?
> > --
> > Chris Davoli
> >
>|||On Fri, 29 Feb 2008 18:14:00 -0800, Chris Davoli
<ChrisDavoli@.discussions.microsoft.com> wrote:
>what is microsoft PSS? Is there a phone or email or something?
Microsoft support. You start with a phone call, pay them some money
with your charge card, and go on from there. Unless of course you
already have a support contract.
Roy Harvey
Beacon Falls, CT|||"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
I would still HIGHLY recommend calling Microsoft.
However, if it's the data portion that's corrupt and not the log, the
recovery scenario may be something like this.
Back up "the tail of the log" as its called (there's a special command for
this but if you've already stopped SQL Server, I don't think you'll be able
to do this.)
Now, if you truly have a good backup from several months ago and haven't
truncated the log since then or in any other way broken the log chain, you
MIGHT be able to restore the database backup from then and then restore the
log and apply that.
HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of
working. With Microsoft helping, I think you could get this down to about 1
in a 100. In other words, not very likely. Sorry.
> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You can also try out ApexSQL Log. It may be able to recover some of the
data. Others are right though - corrupt database is deffinitely a time to
contact Microsoft support!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>|||http://support.microsoft.com/default.aspx?scid=fh%3BEN-US%3Bofferprophone
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:A2EC39C0-4AE9-4E86-91F4-3080E5A0D9DE@.microsoft.com...
> what is microsoft PSS? Is there a phone or email or something?
> --
> Chris Davoli
>
> "Andrew J. Kelly" wrote:
>> As Greg stated you are best to call MS PSS and work directly with someone
>> there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> > Let me correct that. The data part of the database is also corrupt. Is
>> > there
>> > a way to delete the corrupted part of the data base and try a partial
>> > recovery?
>> > --
>> > Chris Davoli
>> >
>>|||Just to add a few tiny bits to Greg's recommendations:
> I would still HIGHLY recommend calling Microsoft.
Just to start with "I agree".
> Back up "the tail of the log" as its called (there's a special command for this but if you've
> already stopped SQL Server, I don't think you'll be able to do this.)
To backup the log of a damaged database one might have to add the NO_TRUNCATE option of the backup
command, like
BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of course
:-) ). We can even copy the ldf file(s) for a database to some other machine, create a (dummy)
database there, stop that SQL Server, delete its database files, slide in the log file(s) for this
damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It is all about
getting the log records from the ldf file to a transaction log file.
Of course, a pre-requisite for all this is an unbroken chain of log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> Let me correct that. The data part of the database is also corrupt. Is there
>> a way to delete the corrupted part of the data base and try a partial
>> recovery?
> I would still HIGHLY recommend calling Microsoft.
> However, if it's the data portion that's corrupt and not the log, the recovery scenario may be
> something like this.
> Back up "the tail of the log" as its called (there's a special command for this but if you've
> already stopped SQL Server, I don't think you'll be able to do this.)
> Now, if you truly have a good backup from several months ago and haven't truncated the log since
> then or in any other way broken the log chain, you MIGHT be able to restore the database backup
> from then and then restore the log and apply that.
> HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of working. With
> Microsoft helping, I think you could get this down to about 1 in a 100. In other words, not very
> likely. Sorry.
>
>> --
>> Chris Davoli
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
> To backup the log of a damaged database one might have to add the
> NO_TRUNCATE option of the backup command, like
> BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
> This is doable even if SQL Server has been stopped (assuming one start SQL
> Server again, of course :-) ). We can even copy the ldf file(s) for a
> database to some other machine, create a (dummy) database there, stop that
> SQL Server, delete its database files, slide in the log file(s) for this
> damaged database, start that SQL Server and now do the log backup using
> NO_TRUNCATE. It is all about getting the log records from the ldf file to
> a transaction log file.
Ah, I wasn't sure that would work. (and I assume you mean delete the log
files, not both database files?)
> Of course, a pre-requisite for all this is an unbroken chain of log
> backups...
A mighty big one. ;-)
This is the sort of thing I might try on a lark if I had spare time, but for
production data, I'd definitely be calling Microsoft. Anything that Tibor
and I might say may or may not work and I can't speak for Tibor (though I'm
sure he'd agree) I'd hate to have you attempt to follow any advice I might
give in this case that makes things worse when Microsoft is very likely to
have a better idea.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> Let me correct that. The data part of the database is also corrupt. Is
>> there
>> a way to delete the corrupted part of the data base and try a partial
>> recovery?
>> I would still HIGHLY recommend calling Microsoft.
>> However, if it's the data portion that's corrupt and not the log, the
>> recovery scenario may be something like this.
>> Back up "the tail of the log" as its called (there's a special command
>> for this but if you've already stopped SQL Server, I don't think you'll
>> be able to do this.)
>> Now, if you truly have a good backup from several months ago and haven't
>> truncated the log since then or in any other way broken the log chain,
>> you MIGHT be able to restore the database backup from then and then
>> restore the log and apply that.
>> HOWEVER, trying this on your own... I give about a chance of 1 in a 1000
>> of working. With Microsoft helping, I think you could get this down to
>> about 1 in a 100. In other words, not very likely. Sorry.
>>
>> --
>> Chris Davoli
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
>|||>> This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of
>> course :-) ). We can even copy the ldf file(s) for a database to some other machine, create a
>> (dummy) database there, stop that SQL Server, delete its database files, slide in the log file(s)
>> for this damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It
>> is all about getting the log records from the ldf file to a transaction log file.
> Ah, I wasn't sure that would work. (and I assume you mean delete the log files, not both database
> files?)
Yep, it work, and I've done that in productions. And I did indeed mean delete all database files.
Say you have a SQL Server installation that is toast, all you have is the ldf file for your critical
database. What you want to do is essentially to turn this ldf file into a log backup file. For this
you need to "get it into" a working SQL Server so you can issue a BACKUP LOG command.
So on on some working SQL Server, you create a database. The sole purpose of this is to get an entry
in "sysdatabases". The database files are not of interest for us. This is why we stop that SQL
Server and delete the database files. And now we copy the lof file from the crasched server (in the
right path, and file name, of course - it need to be the same as the log file for the "dummy"
database we created). So when we not start that SQL Server it will look like any SQL Server for
which the data files for the database are lost - but the log files are there. This is why we can
BACKUP LOG ... WITH NO_TRUNCATE against that database. :-)
> This is the sort of thing I might try on a lark if I had spare time, but for production data, I'd
> definitely be calling Microsoft. Anything that Tibor and I might say may or may not work and I
> can't speak for Tibor (though I'm sure he'd agree) I'd hate to have you attempt to follow any
> advice I might give in this case that makes things worse when Microsoft is very likely to have a
> better idea.
I absolutely agree.
If you don't know, by heart, what measures to take "Oh, that happenened - I know I can do this, I've
done it plenty of times before.", then call MS Support.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eJdwguBfIHA.4376@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
>> To backup the log of a damaged database one might have to add the NO_TRUNCATE option of the
>> backup command, like
>> BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
>> This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of
>> course :-) ). We can even copy the ldf file(s) for a database to some other machine, create a
>> (dummy) database there, stop that SQL Server, delete its database files, slide in the log file(s)
>> for this damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It
>> is all about getting the log records from the ldf file to a transaction log file.
> Ah, I wasn't sure that would work. (and I assume you mean delete the log files, not both database
> files?)
>
>> Of course, a pre-requisite for all this is an unbroken chain of log backups...
> A mighty big one. ;-)
>
> This is the sort of thing I might try on a lark if I had spare time, but for production data, I'd
> definitely be calling Microsoft. Anything that Tibor and I might say may or may not work and I
> can't speak for Tibor (though I'm sure he'd agree) I'd hate to have you attempt to follow any
> advice I might give in this case that makes things worse when Microsoft is very likely to have a
> better idea.
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
>> news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> Let me correct that. The data part of the database is also corrupt. Is there
>> a way to delete the corrupted part of the data base and try a partial
>> recovery?
>> I would still HIGHLY recommend calling Microsoft.
>> However, if it's the data portion that's corrupt and not the log, the recovery scenario may be
>> something like this.
>> Back up "the tail of the log" as its called (there's a special command for this but if you've
>> already stopped SQL Server, I don't think you'll be able to do this.)
>> Now, if you truly have a good backup from several months ago and haven't truncated the log since
>> then or in any other way broken the log chain, you MIGHT be able to restore the database backup
>> from then and then restore the log and apply that.
>> HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of working. With
>> Microsoft helping, I think you could get this down to about 1 in a 100. In other words, not
>> very likely. Sorry.
>>
>> --
>> Chris Davoli
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>

Bad Logs

Let me correct that. The data part of the database is also corrupt. Is there
a way to delete the corrupted part of the data base and try a partial
recovery?
Chris Davoli
As Greg stated you are best to call MS PSS and work directly with someone
there.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>
|||what is microsoft PSS? Is there a phone or email or something?
Chris Davoli
"Andrew J. Kelly" wrote:

> As Greg stated you are best to call MS PSS and work directly with someone
> there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>
|||On Fri, 29 Feb 2008 18:14:00 -0800, Chris Davoli
<ChrisDavoli@.discussions.microsoft.com> wrote:

>what is microsoft PSS? Is there a phone or email or something?
Microsoft support. You start with a phone call, pay them some money
with your charge card, and go on from there. Unless of course you
already have a support contract.
Roy Harvey
Beacon Falls, CT
|||"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
I would still HIGHLY recommend calling Microsoft.
However, if it's the data portion that's corrupt and not the log, the
recovery scenario may be something like this.
Back up "the tail of the log" as its called (there's a special command for
this but if you've already stopped SQL Server, I don't think you'll be able
to do this.)
Now, if you truly have a good backup from several months ago and haven't
truncated the log since then or in any other way broken the log chain, you
MIGHT be able to restore the database backup from then and then restore the
log and apply that.
HOWEVER, trying this on your own... I give about a chance of 1 in a 1000 of
working. With Microsoft helping, I think you could get this down to about 1
in a 100. In other words, not very likely. Sorry.

> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You can also try out ApexSQL Log. It may be able to recover some of the
data. Others are right though - corrupt database is deffinitely a time to
contact Microsoft support!
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?
> --
> Chris Davoli
>
|||http://support.microsoft.com/default.aspx?scid=fh%3BEN-US%3Bofferprophone
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:A2EC39C0-4AE9-4E86-91F4-3080E5A0D9DE@.microsoft.com...[vbcol=seagreen]
> what is microsoft PSS? Is there a phone or email or something?
> --
> Chris Davoli
>
> "Andrew J. Kelly" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
> To backup the log of a damaged database one might have to add the
> NO_TRUNCATE option of the backup command, like
> BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE
> This is doable even if SQL Server has been stopped (assuming one start SQL
> Server again, of course :-) ). We can even copy the ldf file(s) for a
> database to some other machine, create a (dummy) database there, stop that
> SQL Server, delete its database files, slide in the log file(s) for this
> damaged database, start that SQL Server and now do the log backup using
> NO_TRUNCATE. It is all about getting the log records from the ldf file to
> a transaction log file.
Ah, I wasn't sure that would work. (and I assume you mean delete the log
files, not both database files?)

> Of course, a pre-requisite for all this is an unbroken chain of log
> backups...
A mighty big one. ;-)
This is the sort of thing I might try on a lark if I had spare time, but for
production data, I'd definitely be calling Microsoft. Anything that Tibor
and I might say may or may not work and I can't speak for Tibor (though I'm
sure he'd agree) I'd hate to have you attempt to follow any advice I might
give in this case that makes things worse when Microsoft is very likely to
have a better idea.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
Craig
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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig
|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:

> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now jus
t
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow 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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan.
I
> have set it to delete files older than 1 day. This used to work, but now j
ust
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now i
t
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.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 woul
d
> 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 t
o
> local drive), then you will need to modify the above (with respect to wher
e
> 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow 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/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> Below 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/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
> >I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> > have set it to delete files older than 1 day. This used to work, but now just
> > backs up without deleting the old files.
> >
> > Any help would be appreciated,
> > Craig
>
>

Backups not deleted.

I am running sql server 2000 on windows 2000. i have my backups set to delete after a week. they are not deleting, can anyone tell me why this is happening?? the backups filled the hard drive.open up your scheduled job, then open up the step that executes the backups. You should see an SQL statement like "EXECUTE master.dbo.xp_sqlmaint N'-PlanName...."

Post the statement for us to review, and while you are at it make sure that in the Advanced tab of the step editor you have an output file set to record results.|||here is the step.

EXECUTE master.dbo.xp_sqlmaint N'-PlanID 88C31DB1-FC24-42E7-AE79-CF2FA5E6C26B -Rpt "D:\Program Files\Microsoft SQL Server\MSSQL\LOG\Backup MKS4.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1WEEKS -BkExt "BAK"'|||OK, here are a few things to try, in this order:
1) Drop the quotes around your -BkExt parameter value:
Change this: -BkExt "BAK"
...to this: -BkExt BAK
2) Specify a backup directory after your -BkUpDB parameter, like this: -BkUpDB F:\SQLBackups
3) Try changing -DelBkUps 1WEEKS to -DelBkUps 7Days

Also, I find it helpful to use the -CrBkSubDir, so that all my database backups are organized in different directories.

And again, save the output from your job to a file, so you can trap all the errors and messages that are generated.

Backups files not deleted with maintenance plan

Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.
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/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>
|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> 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/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>
|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>

Backups files not deleted with maintenance plan

Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below 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/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below 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/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
>> The backup works perfectly fine, but it does not delete the backup files
>> scheduled to cleanup files older then a week. Any suggestions where to
>> look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below 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/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
>> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
>> week. Any suggestions where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually deleted backups for around
>> 40 databases.
>> Thanks a lot.
>> Imran.
>>
>|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>> Thanks for the link and the details. It cannot be a permissions issue or
>> a sharing violation since I have the same issue on two servers, the
>> development server and the live server both don't delete backup files.
>> I ran the maintenance plan right now and there wasn't any other process
>> holding the backup files. but still the files were not deleted. The
>> backup log did not indicate any errors relating to deletion of files,
>> rather it does not even show the attempt to delete the older files.
>> Any clues ?
>> Imran.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>> Below 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/
>>
>> "Imran Aziz" <imran@.tb2.net> wrote in message
>> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>> Hello All,
>> I have a SQL server maintenance plan setup with my SQLServer 2000
>> MMC. The backup works perfectly fine, but it does not delete the backup
>> files scheduled to cleanup files older then a week. Any suggestions
>> where to look to sort this out?
>> The backups take up the disk space and I have to go in and manually
>> deleted backups for around 40 databases.
>> Thanks a lot.
>> Imran.
>>
>>
>

Backups files not deleted with maintenance plan

Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.Below 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/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...[
vbcol=seagreen]
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works
> perfectly fine, but it does not delete the backup files scheduled to clean
up files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually delete
d backups for around 40
> databases.
> Thanks a lot.
> Imran.
>[/vbcol]|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.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/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>|||Do you have any databases in simple recovery mode included in the plan? I've
seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the m
aint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...[
vbcol=seagreen]
> Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since
> I have the same issue on two servers, the development server and the live
server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process ho
lding the backup files.
> but still the files were not deleted. The backup log did not indicate any
errors relating to
> deletion of files, rather it does not even show the attempt to delete the
older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>