deleting a sql backup file

My backups job are failing at the delete old backup step
because of not being to delete a backup file. When I try
to manually delete this file I get the sharing violation
error message - that someone or something process may be
using the file. I know for a fact that it is not someone
and I need to delete this file. I take ownership of the
file with full control permissions but still can't delete
this file. I hate to bounce a server just to be able to
delete a file. Does anyone know what I can do to delete
this file? Is there a way to have sql server or agent
take control of the files I want the jobs to delete? I
appreciate your help. Thanks
LisaLisa,
This happened to me once a while ago and it was because sql server was still
verifying the backup file. When you use database maintenance plans, one of
the options is to verify the file after the backup is taken. If this is not
the case you're going to have to find a windows utility to find which
windows process has the file locked, i think i found one of these utilities
at www.sysinternals.com a while ago, but i'm not sure.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> My backups job are failing at the delete old backup step
> because of not being to delete a backup file. When I try
> to manually delete this file I get the sharing violation
> error message - that someone or something process may be
> using the file. I know for a fact that it is not someone
> and I need to delete this file. I take ownership of the
> file with full control permissions but still can't delete
> this file. I hate to bounce a server just to be able to
> delete a file. Does anyone know what I can do to delete
> this file? Is there a way to have sql server or agent
> take control of the files I want the jobs to delete? I
> appreciate your help. Thanks
> Lisa|||Thank you so much Carlos. The "verify integrity of backup
upon completion" was checked. If this is locking the
files and uncheck that box, isn't that a bad thing? What
is the impact on the integrity of my backups. I will
check the winternals website also. Thanks.
Lisa.
>--Original Message--
>Lisa,
>This happened to me once a while ago and it was because
sql server was still
>verifying the backup file. When you use database
maintenance plans, one of
>the options is to verify the file after the backup is
taken. If this is not
>the case you're going to have to find a windows utility
to find which
>windows process has the file locked, i think i found one
of these utilities
>at www.sysinternals.com a while ago, but i'm not sure.
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> My backups job are failing at the delete old backup step
>> because of not being to delete a backup file. When I
try
>> to manually delete this file I get the sharing violation
>> error message - that someone or something process may be
>> using the file. I know for a fact that it is not
someone
>> and I need to delete this file. I take ownership of the
>> file with full control permissions but still can't
delete
>> this file. I hate to bounce a server just to be able to
>> delete a file. Does anyone know what I can do to delete
>> this file? Is there a way to have sql server or agent
>> take control of the files I want the jobs to delete? I
>> appreciate your help. Thanks
>> Lisa
>
>.
>|||That option is IMO not that useful. To check out what it does, read about
the VERIFYONLY option to the RESTORE in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> Thank you so much Carlos. The "verify integrity of backup
> upon completion" was checked. If this is locking the
> files and uncheck that box, isn't that a bad thing? What
> is the impact on the integrity of my backups. I will
> check the winternals website also. Thanks.
> Lisa.
>
> >--Original Message--
> >Lisa,
> >This happened to me once a while ago and it was because
> sql server was still
> >verifying the backup file. When you use database
> maintenance plans, one of
> >the options is to verify the file after the backup is
> taken. If this is not
> >the case you're going to have to find a windows utility
> to find which
> >windows process has the file locked, i think i found one
> of these utilities
> >at www.sysinternals.com a while ago, but i'm not sure.
> >
> >--
> >Carlos E. Rojas
> >SQL Server MVP
> >Co-Author SQL Server 2000 Programming by Example
> >
> >
> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> >> My backups job are failing at the delete old backup step
> >> because of not being to delete a backup file. When I
> try
> >> to manually delete this file I get the sharing violation
> >> error message - that someone or something process may be
> >> using the file. I know for a fact that it is not
> someone
> >> and I need to delete this file. I take ownership of the
> >> file with full control permissions but still can't
> delete
> >> this file. I hate to bounce a server just to be able to
> >> delete a file. Does anyone know what I can do to delete
> >> this file? Is there a way to have sql server or agent
> >> take control of the files I want the jobs to delete? I
> >> appreciate your help. Thanks
> >>
> >> Lisa
> >
> >
> >.
> >|||agree completely...
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
> That option is IMO not that useful. To check out what it does, read about
> the VERIFYONLY option to the RESTORE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lisa Trueman" <anonymous@.discussions.microsoft.com> wrote in message
> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
> > Thank you so much Carlos. The "verify integrity of backup
> > upon completion" was checked. If this is locking the
> > files and uncheck that box, isn't that a bad thing? What
> > is the impact on the integrity of my backups. I will
> > check the winternals website also. Thanks.
> >
> > Lisa.
> >
> >
> > >--Original Message--
> > >Lisa,
> > >This happened to me once a while ago and it was because
> > sql server was still
> > >verifying the backup file. When you use database
> > maintenance plans, one of
> > >the options is to verify the file after the backup is
> > taken. If this is not
> > >the case you're going to have to find a windows utility
> > to find which
> > >windows process has the file locked, i think i found one
> > of these utilities
> > >at www.sysinternals.com a while ago, but i'm not sure.
> > >
> > >--
> > >Carlos E. Rojas
> > >SQL Server MVP
> > >Co-Author SQL Server 2000 Programming by Example
> > >
> > >
> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
> > wrote in message
> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
> > >> My backups job are failing at the delete old backup step
> > >> because of not being to delete a backup file. When I
> > try
> > >> to manually delete this file I get the sharing violation
> > >> error message - that someone or something process may be
> > >> using the file. I know for a fact that it is not
> > someone
> > >> and I need to delete this file. I take ownership of the
> > >> file with full control permissions but still can't
> > delete
> > >> this file. I hate to bounce a server just to be able to
> > >> delete a file. Does anyone know what I can do to delete
> > >> this file? Is there a way to have sql server or agent
> > >> take control of the files I want the jobs to delete? I
> > >> appreciate your help. Thanks
> > >>
> > >> Lisa
> > >
> > >
> > >.
> > >
>|||Thank you very much.
>--Original Message--
>agree completely...
>--
>Carlos E. Rojas
>SQL Server MVP
>Co-Author SQL Server 2000 Programming by Example
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:eX6x4AD5DHA.1596@.TK2MSFTNGP10.phx.gbl...
>> That option is IMO not that useful. To check out what
it does, read about
>> the VERIFYONLY option to the RESTORE in Books Online.
>> --
>> Tibor Karaszi, SQL Server MVP
>> Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>> "Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> > Thank you so much Carlos. The "verify integrity of
backup
>> > upon completion" was checked. If this is locking the
>> > files and uncheck that box, isn't that a bad thing?
What
>> > is the impact on the integrity of my backups. I will
>> > check the winternals website also. Thanks.
>> >
>> > Lisa.
>> >
>> >
>> > >--Original Message--
>> > >Lisa,
>> > >This happened to me once a while ago and it was
because
>> > sql server was still
>> > >verifying the backup file. When you use database
>> > maintenance plans, one of
>> > >the options is to verify the file after the backup is
>> > taken. If this is not
>> > >the case you're going to have to find a windows
utility
>> > to find which
>> > >windows process has the file locked, i think i found
one
>> > of these utilities
>> > >at www.sysinternals.com a while ago, but i'm not
sure.
>> > >
>> > >--
>> > >Carlos E. Rojas
>> > >SQL Server MVP
>> > >Co-Author SQL Server 2000 Programming by Example
>> > >
>> > >
>> > >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> > wrote in message
>> > >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> > >> My backups job are failing at the delete old
backup step
>> > >> because of not being to delete a backup file.
When I
>> > try
>> > >> to manually delete this file I get the sharing
violation
>> > >> error message - that someone or something process
may be
>> > >> using the file. I know for a fact that it is not
>> > someone
>> > >> and I need to delete this file. I take ownership
of the
>> > >> file with full control permissions but still can't
>> > delete
>> > >> this file. I hate to bounce a server just to be
able to
>> > >> delete a file. Does anyone know what I can do to
delete
>> > >> this file? Is there a way to have sql server or
agent
>> > >> take control of the files I want the jobs to
delete? I
>> > >> appreciate your help. Thanks
>> > >>
>> > >> Lisa
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>Lis|||Thanks Tibor
>--Original Message--
>That option is IMO not that useful. To check out what it
does, read about
>the VERIFYONLY option to the RESTORE in Books Online.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lisa Trueman" <anonymous@.discussions.microsoft.com>
wrote in message
>news:492101c3e42b$e9226d20$a501280a@.phx.gbl...
>> Thank you so much Carlos. The "verify integrity of
backup
>> upon completion" was checked. If this is locking the
>> files and uncheck that box, isn't that a bad thing?
What
>> is the impact on the integrity of my backups. I will
>> check the winternals website also. Thanks.
>> Lisa.
>>
>> >--Original Message--
>> >Lisa,
>> >This happened to me once a while ago and it was because
>> sql server was still
>> >verifying the backup file. When you use database
>> maintenance plans, one of
>> >the options is to verify the file after the backup is
>> taken. If this is not
>> >the case you're going to have to find a windows utility
>> to find which
>> >windows process has the file locked, i think i found
one
>> of these utilities
>> >at www.sysinternals.com a while ago, but i'm not sure.
>> >
>> >--
>> >Carlos E. Rojas
>> >SQL Server MVP
>> >Co-Author SQL Server 2000 Programming by Example
>> >
>> >
>> >"Lisa Trueman" <anonymous@.discussions.microsoft.com>
>> wrote in message
>> >news:45b601c3e428$8930b000$a001280a@.phx.gbl...
>> >> My backups job are failing at the delete old backup
step
>> >> because of not being to delete a backup file. When I
>> try
>> >> to manually delete this file I get the sharing
violation
>> >> error message - that someone or something process
may be
>> >> using the file. I know for a fact that it is not
>> someone
>> >> and I need to delete this file. I take ownership of
the
>> >> file with full control permissions but still can't
>> delete
>> >> this file. I hate to bounce a server just to be
able to
>> >> delete a file. Does anyone know what I can do to
delete
>> >> this file? Is there a way to have sql server or
agent
>> >> take control of the files I want the jobs to
delete? I
>> >> appreciate your help. Thanks
>> >>
>> >> Lisa
>> >
>> >
>> >.
>> >
>
>.
>

Deletes

In a heavly indexed table, why does a delete step run forever?
JimBecuase it has to change the data pages and the indexes...

INSERTS should take even longer...

What does the DELETE statement look like?

And what about the DDL for the table...|||Originally posted by Brett Kaiser
Becuase it has to change the data pages and the indexes...

INSERTS should take even longer...

What does the DELETE statement look like?

And what about the DDL for the table...
Thats what i was thinking

the statment is basiacaly
delete tablename
where date = '2003/12/12'
and direction = 'E'

simple enought but there are about 12 indexes on the table. There is also 20 some od columns to the table so its got a bit to go through. Sounds like a delete step isnt the right thing to do...but I dont know of anything else. Rebuilding the table has proven to be faster though, guess im gona go back to that.
Jim|||apparently you have a long table with quite a few records meeting the criteria, because even if there are 12 indexes it should go through delete farely fast if the number of rows affected it significantly less than the total number of rows (<500)|||Originally posted by ms_sql_dba
apparently you have a long table with quite a few records meeting the criteria, because even if there are 12 indexes it should go through delete farely fast if the number of rows affected it significantly less than the total number of rows (<500)
we are trying to delete about 500000 rows out of a 23Million row table
hehe
Jim|||Do any of your indexes have date and/or direction?|||Originally posted by Brett Kaiser
Do any of your indexes have date and/or direction?

Direction yes, no date indexes...coleage of mine was thinking that it would be benificial if we had a date index....
Jim|||Well hell yes...

Do a show estimated execution plan, and tell us what you get...

I'm thinking table scan...

Edit is direction part of a composite of another index, or is it all by it's lonesome?

If it's alone, just add date to that one, and go have a couple of beers...it'll take a while....

better to schedule a script to run overnight...

let us know how long it takes if you do do it...|||An informative an lengthy discourse on indexes

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30090&SearchTerms=index,intersection|||you'd really benefit having date and direction as an index (order depends on selectivity of direction vs. date, the less selective field should be first).|||Make sure you have a clustered index on the table as well as heaps reclaim empty space and deletes on fragmented tables take longer than normal.

dbcc showcontig on the table will tell you how fragemented the table is.

HTH