deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> > Hi,
> >
> > I backed up my transaction log in order to perform a point in time
> > restore.
> >
> > I backed it up to an ad hoc folder. The database actually does backup the
> > log file once a night to a different location. I'm going to change this to
> > once every thirty minutes.
> >
> > Can I delete this ad hoc transaction log file backup, or do future backups
> > depend on it?
> >
> > Thanks very much for your ideas on this. I just want to be safe!
> >
> > Many thanks!
> >
> > Ant
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
Ant
Hello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant
|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

deleting a tx log backup

Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:

> Hello,
> If you have a FULL database backup taken after the specific trasnaction lo
g
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>

Delete using another tables values

Hi all,
I am trying to perform a delete that I could achieve in Access but
need to do this in sql2000.
I have two tables Warranty and Registrations. I would like to delete
all items in the warranty table where there is a match in
registrations on a common field.
I access the query would be:
DELETE warranty.*
FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
registrations.vins;

But cannot replicate this in SQL server?

Any help would be much appreciated.

Thanks
SamOn 23 Sep 2004 02:40:24 -0700, SG wrote:

>I access the query would be:
>DELETE warranty.*
>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>registrations.vins;
>But cannot replicate this in SQL server?

Hi Sam,

You're almost there. The Transact-SQL version of this would be

DELETE warranty
FROM warranty
INNER JOIN registration
ON warranty.BBMQCE = registrations.vins

Yes - you only need to drop the .* !!!

However, the above is proprietary code that will not port well to other
databases. If you want portability, use the ANSI-standard delete syntax
instead:

DELETE FROM warranty
WHERE NOT EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

(both queries untested - beware of spelling errors!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Many thanks - i was so nearly there!
Sam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hugo Kornelis wrote:

> On 23 Sep 2004 02:40:24 -0700, SG wrote:
>
>>I access the query would be:
>>DELETE warranty.*
>>FROM warranty INNER JOIN registrations ON warranty.BBMQCE =
>>registrations.vins;
>>
>>But cannot replicate this in SQL server?
>
> Hi Sam,
> You're almost there. The Transact-SQL version of this would be
> DELETE warranty
> FROM warranty
> INNER JOIN registration
> ON warranty.BBMQCE = registrations.vins
> Yes - you only need to drop the .* !!!
>
> However, the above is proprietary code that will not port well to other
> databases. If you want portability, use the ANSI-standard delete syntax
> instead:
> DELETE FROM warranty
> WHERE NOT EXISTS (SELECT *
> FROM registration
> WHERE warranty.BBMQCE = registrations.vins)
> (both queries untested - beware of spelling errors!)
> Best, Hugo

DELETE FROM warranty
WHERE EXISTS (SELECT *
FROM registration
WHERE warranty.BBMQCE = registrations.vins)

There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches|||On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:

>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches

Hi Andrey,

Good catch! Thanks for correcting my mistake.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On Sat, 25 Sep 2004 06:01:13 GMT, Andrey wrote:
>
>>There shouldn't be *NOT* in WHERE clause, because SG wants to delete all matches
>
> Hi Andrey,
> Good catch! Thanks for correcting my mistake.
> Best, Hugo

You're welcome :)

Delete of 2.5 GB

Hi,
I have to perform a delete on a table. About 73.000
records that actually need 2.5 GB of storage. I simply
will perform a delete from table where .... Should i
consider doing this after hours ? What impact should i
consider for this action ? this is a 24x7 DB Server.
I'm planning the delete and after do a dbcc clean table to
organize the table and index.
Any suggestions or recommendations ?
Thanks in advance
Mike
i'd recommend deleting the data in small portions instead of one delete
statement that deletes all 73k rows at once. deleting small groups of
data should have minimal to no impact on your users. if you're using
full or bulk-logged recovery, be sure to do transaction log backups at
appropriate times during your deletes.
Mike wrote:

> Hi,
> I have to perform a delete on a table. About 73.000
> records that actually need 2.5 GB of storage. I simply
> will perform a delete from table where .... Should i
> consider doing this after hours ? What impact should i
> consider for this action ? this is a 24x7 DB Server.
> I'm planning the delete and after do a dbcc clean table to
> organize the table and index.
> Any suggestions or recommendations ?
> Thanks in advance
> Mike
|||Not sure if this applies, but if you are deleting all the records in the
table you can use the TRUNCATE command.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Delete of 2.5 GB

Hi,
I have to perform a delete on a table. About 73.000
records that actually need 2.5 GB of storage. I simply
will perform a delete from table where .... Should i
consider doing this after hours ? What impact should i
consider for this action ' this is a 24x7 DB Server.
I'm planning the delete and after do a dbcc clean table to
organize the table and index.
Any suggestions or recommendations '
Thanks in advance
MikeI agree with Julie on some points but not all. Always backup the db first.
I would do the deletes in smaller batches and you might need to backup the
tran log in between. If the rows take up 2.5GB of space then you will have
at least that much in the tran log. DBCC CLEANTABLE may not buy you
anything here since you aren't dropping any columns. I would do a DBREINDEX
but DO NOT issue a Shrinkfile unless you absolutely need that space. A
Shrinkfile will most likely undo all you just accomplished with the
DBREINDEX.
Andrew J. Kelly
SQL Server MVP
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:12c0d01c4433e$ffa93890$a301280a@.phx
.gbl...[vbcol=seagreen]
> Presonally I would set up a job to
> 1. Backup the database
> 2. Delete the data
> 3. CLEANDB
> 4. DBREINDEX
> 5. ShrinkFile
> The major inpact will be on any batch jobs happening on
> that table at the same time i.e it may not be using it but
> it may be linked to the process, which could cause locking.
> J
>
> to|||i'd recommend deleting the data in small portions instead of one delete
statement that deletes all 73k rows at once. deleting small groups of
data should have minimal to no impact on your users. if you're using
full or bulk-logged recovery, be sure to do transaction log backups at
appropriate times during your deletes.
Mike wrote:

> Hi,
> I have to perform a delete on a table. About 73.000
> records that actually need 2.5 GB of storage. I simply
> will perform a delete from table where .... Should i
> consider doing this after hours ? What impact should i
> consider for this action ' this is a 24x7 DB Server.
> I'm planning the delete and after do a dbcc clean table to
> organize the table and index.
> Any suggestions or recommendations '
> Thanks in advance
> Mike|||Agreed, you only need to do the shrink file if want to
want to get your disk space back.
Totally agree with cleantable.
Deletion not too sure I agree, personally prefer to get
it al over at once.
Shrinkfile affecting DBREINDEX, i didn't know that
thanks, but wouldn't it just squeeze the pages together
rather than changing the order ?
J

>--Original Message--
>I agree with Julie on some points but not all. Always
backup the db first.
>I would do the deletes in smaller batches and you might
need to backup the
>tran log in between. If the rows take up 2.5GB of space
then you will have
>at least that much in the tran log. DBCC CLEANTABLE may
not buy you
>anything here since you aren't dropping any columns. I
would do a DBREINDEX
>but DO NOT issue a Shrinkfile unless you absolutely need
that space. A
>Shrinkfile will most likely undo all you just
accomplished with the
>DBREINDEX.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:12c0d01c4433e$ffa93890$a301280a@.phx
.gbl...
but[vbcol=seagreen]
locking.[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||Not sure if this applies, but if you are deleting all the records in the
table you can use the TRUNCATE command.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Julie wrote:

> Shrinkfile affecting DBREINDEX, i didn't know that
> thanks, but wouldn't it just squeeze the pages together
> rather than changing the order ?
it changes the page order dramatically. in fact, it's sort of a goofy
situation. you shrink the db to get all the data to the front of the
file. after you shrink it, it's most likely very heavily fragmented.
so then you defrag it. when you defrag it, it moves pages out into the
free space in the db file(s), so now your pages are no longer all at the
front of the file.|||Thanks ch,
I never knew that.
J

>--Original Message--
>Julie wrote:
>
>it changes the page order dramatically. in fact, it's
sort of a goofy
>situation. you shrink the db to get all the data to the
front of the
>file. after you shrink it, it's most likely very
heavily fragmented.
>so then you defrag it. when you defrag it, it moves
pages out into the
>free space in the db file(s), so now your pages are no
longer all at the
>front of the file.
>.
>

Delete of 2.5 GB

Hi,
I have to perform a delete on a table. About 73.000
records that actually need 2.5 GB of storage. I simply
will perform a delete from table where .... Should i
consider doing this after hours ? What impact should i
consider for this action ' this is a 24x7 DB Server.
I'm planning the delete and after do a dbcc clean table to
organize the table and index.
Any suggestions or recommendations '
Thanks in advance
MikePresonally I would set up a job to
1. Backup the database
2. Delete the data
3. CLEANDB
4. DBREINDEX
5. ShrinkFile
The major inpact will be on any batch jobs happening on
that table at the same time i.e it may not be using it but
it may be linked to the process, which could cause locking.
J
>--Original Message--
>Hi,
>I have to perform a delete on a table. About 73.000
>records that actually need 2.5 GB of storage. I simply
>will perform a delete from table where .... Should i
>consider doing this after hours ? What impact should i
>consider for this action ' this is a 24x7 DB Server.
>I'm planning the delete and after do a dbcc clean table
to
>organize the table and index.
>Any suggestions or recommendations '
>Thanks in advance
>Mike
>.
>|||I agree with Julie on some points but not all. Always backup the db first.
I would do the deletes in smaller batches and you might need to backup the
tran log in between. If the rows take up 2.5GB of space then you will have
at least that much in the tran log. DBCC CLEANTABLE may not buy you
anything here since you aren't dropping any columns. I would do a DBREINDEX
but DO NOT issue a Shrinkfile unless you absolutely need that space. A
Shrinkfile will most likely undo all you just accomplished with the
DBREINDEX.
--
Andrew J. Kelly
SQL Server MVP
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:12c0d01c4433e$ffa93890$a301280a@.phx.gbl...
> Presonally I would set up a job to
> 1. Backup the database
> 2. Delete the data
> 3. CLEANDB
> 4. DBREINDEX
> 5. ShrinkFile
> The major inpact will be on any batch jobs happening on
> that table at the same time i.e it may not be using it but
> it may be linked to the process, which could cause locking.
> J
>
> >--Original Message--
> >Hi,
> >
> >I have to perform a delete on a table. About 73.000
> >records that actually need 2.5 GB of storage. I simply
> >will perform a delete from table where .... Should i
> >consider doing this after hours ? What impact should i
> >consider for this action ' this is a 24x7 DB Server.
> >
> >I'm planning the delete and after do a dbcc clean table
> to
> >organize the table and index.
> >
> >Any suggestions or recommendations '
> >
> >Thanks in advance
> >Mike
> >.
> >|||i'd recommend deleting the data in small portions instead of one delete
statement that deletes all 73k rows at once. deleting small groups of
data should have minimal to no impact on your users. if you're using
full or bulk-logged recovery, be sure to do transaction log backups at
appropriate times during your deletes.
Mike wrote:
> Hi,
> I have to perform a delete on a table. About 73.000
> records that actually need 2.5 GB of storage. I simply
> will perform a delete from table where .... Should i
> consider doing this after hours ? What impact should i
> consider for this action ' this is a 24x7 DB Server.
> I'm planning the delete and after do a dbcc clean table to
> organize the table and index.
> Any suggestions or recommendations '
> Thanks in advance
> Mike|||Agreed, you only need to do the shrink file if want to
want to get your disk space back.
Totally agree with cleantable.
Deletion not too sure I agree, personally prefer to get
it al over at once.
Shrinkfile affecting DBREINDEX, i didn't know that
thanks, but wouldn't it just squeeze the pages together
rather than changing the order ?
J
>--Original Message--
>I agree with Julie on some points but not all. Always
backup the db first.
>I would do the deletes in smaller batches and you might
need to backup the
>tran log in between. If the rows take up 2.5GB of space
then you will have
>at least that much in the tran log. DBCC CLEANTABLE may
not buy you
>anything here since you aren't dropping any columns. I
would do a DBREINDEX
>but DO NOT issue a Shrinkfile unless you absolutely need
that space. A
>Shrinkfile will most likely undo all you just
accomplished with the
>DBREINDEX.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:12c0d01c4433e$ffa93890$a301280a@.phx.gbl...
>> Presonally I would set up a job to
>> 1. Backup the database
>> 2. Delete the data
>> 3. CLEANDB
>> 4. DBREINDEX
>> 5. ShrinkFile
>> The major inpact will be on any batch jobs happening on
>> that table at the same time i.e it may not be using it
but
>> it may be linked to the process, which could cause
locking.
>> J
>>
>> >--Original Message--
>> >Hi,
>> >
>> >I have to perform a delete on a table. About 73.000
>> >records that actually need 2.5 GB of storage. I simply
>> >will perform a delete from table where .... Should i
>> >consider doing this after hours ? What impact should i
>> >consider for this action ' this is a 24x7 DB Server.
>> >
>> >I'm planning the delete and after do a dbcc clean
table
>> to
>> >organize the table and index.
>> >
>> >Any suggestions or recommendations '
>> >
>> >Thanks in advance
>> >Mike
>> >.
>> >
>
>.
>|||Not sure if this applies, but if you are deleting all the records in the
table you can use the TRUNCATE command.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Julie wrote:
> Shrinkfile affecting DBREINDEX, i didn't know that
> thanks, but wouldn't it just squeeze the pages together
> rather than changing the order ?
it changes the page order dramatically. in fact, it's sort of a goofy
situation. you shrink the db to get all the data to the front of the
file. after you shrink it, it's most likely very heavily fragmented.
so then you defrag it. when you defrag it, it moves pages out into the
free space in the db file(s), so now your pages are no longer all at the
front of the file.|||Thanks ch,
I never knew that.
J
>--Original Message--
>Julie wrote:
>> Shrinkfile affecting DBREINDEX, i didn't know that
>> thanks, but wouldn't it just squeeze the pages together
>> rather than changing the order ?
>it changes the page order dramatically. in fact, it's
sort of a goofy
>situation. you shrink the db to get all the data to the
front of the
>file. after you shrink it, it's most likely very
heavily fragmented.
>so then you defrag it. when you defrag it, it moves
pages out into the
>free space in the db file(s), so now your pages are no
longer all at the
>front of the file.
>.
>|||Hi,
Thanks for your comments, but now i have a problem:
1. I backed up the database
2. i've made the delete in small batches
3. This table only has a clustered index on an PK Column,
i have performed a DBREINDEX on that Pk clustered index.
Results:
name
C29___BINARIES
rows reserved data index_size unused
221392 4902000 KB 2724296 KB 112 KB 2177592 KB
How can i free the unused table space '? Why the
dbreindex didin't release this space '
Sorry :) Can you help me ?
Thanks again
Mike|||dbcc updateusage (0) with count_rows
it may actually be freed but not recorded yet.
Mike wrote:
> Hi,
> Thanks for your comments, but now i have a problem:
> 1. I backed up the database
> 2. i've made the delete in small batches
> 3. This table only has a clustered index on an PK Column,
> i have performed a DBREINDEX on that Pk clustered index.
> Results:
> name
> C29___BINARIES
> rows reserved data index_size unused
> 221392 4902000 KB 2724296 KB 112 KB 2177592 KB
> How can i free the unused table space '? Why the
> dbreindex didin't release this space '
> Sorry :) Can you help me ?
> Thanks again
> Mike