Deleting records in the logfile

I have a database that is used to store a lot of data. We load the data on a
daily basis, several thousand records per day. The Log file is not needed,
so whats the best way to delete the records in it and reduce the size

Thanks

Derrick"Derrick King" <derrick.king@.bradford.gov.uk> wrote in message
news:c1l7ag$ejl$1@.newsreaderm1.core.theplanet.net. ..
> I have a database that is used to store a lot of data. We load the data on
a
> daily basis, several thousand records per day. The Log file is not needed,
> so whats the best way to delete the records in it and reduce the size
> Thanks
> Derrick

You don't mention which version of MSSQL you have, but assuming it's 2000,
then see "Recovery Models" in Books Online. If you don't need transaction
log backups, the easiest solution is probably to set the database to Simple
recovery mode, which will automatically recover log space if possible.

If that's not acceptable, then you can consider transaction log backups (if
you don't already do that), which will truncate the log. Truncating the log
frees up log space but does not make it physically smaller, so you may also
need to use DBCC SHRINKFILE - see "Shrinking Databases".

Simon

Deleting Large Quantiy of Rows

Hi Guys,
I need a little advice. I need to delete 250000 rows on a monthly basis and
my users are using MSDE 2000 with merge replication, publisher is SQL Server
2000 Standard.
The users will never be uploading or making changes to this table. Is there
any way i can make this a quick delete as currently for each row that is in
the Article published table sql is sending down a delete statement for each
row to the subscriber. Thus taking longer than expected.
Any ideas or advice would be greatly apprecitaed.
Thanks, Tim.
From what you are saying it looks like these rows on the subscriber are read
only and the data flow is only from the publisher to the subscriber for this
table. If so remove this table from the merge publication, put it in a
transactional publication and do your deletes through a stored procedure and
replicate the execution of the stored procedure.
Hilary Cotter
Looking for a SQL Server replication book
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I need a little advice. I need to delete 250000 rows on a monthly basis
and
> my users are using MSDE 2000 with merge replication, publisher is SQL
Server
> 2000 Standard.
> The users will never be uploading or making changes to this table. Is
there
> any way i can make this a quick delete as currently for each row that is
in
> the Article published table sql is sending down a delete statement for
each
> row to the subscriber. Thus taking longer than expected.
> Any ideas or advice would be greatly apprecitaed.
> Thanks, Tim.
>
|||Hi Hilary,
Thanks for your response, this is not going to be possible as we use the
ActiveX control and the publications have been hard coded. Which i know is
not good. Is there any other way that we could perhaps specify the order
that things get run in I.E sp_AddScriptexec. I have worked a way out with
the system tables that we could remove generations. However i would need to
make sure that sp_addscriptexec was fired first in the replication process.
Thanks, Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
> From what you are saying it looks like these rows on the subscriber are
> read
> only and the data flow is only from the publisher to the subscriber for
> this
> table. If so remove this table from the merge publication, put it in a
> transactional publication and do your deletes through a stored procedure
> and
> replicate the execution of the stored procedure.
> --
> Hilary Cotter
> Looking for a SQL Server replication book
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:%23kd7psfrFHA.3604@.tk2msftngp13.phx.gbl...
> and
> Server
> there
> in
> each
>
|||I think your best option would be to drop the subscription (possibly drop
the publication as well to drop the triggers which will slow the delete), do
the delete, and then recreate the publication and subscription and do a no
sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi Hilary,
> Thanks for your response, this is not going to be possible as we use the
> ActiveX control and the publications have been hard coded. Which i know is
> not good. Is there any other way that we could perhaps specify the order
> that things get run in I.E sp_AddScriptexec. I have worked a way out with
> the system tables that we could remove generations. However i would need
to
> make sure that sp_addscriptexec was fired first in the replication
process.[vbcol=seagreen]
> Thanks, Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23boP2ShrFHA.1788@.tk2msftngp13.phx.gbl...
is
>
|||Hi Hilary,
Thanks for that. What would happen to the information that was in the table
on the subscriber? Would that get deleted locally, as this information
would no longer be needed.
Again Thanks for all you help.
Tim.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
>I think your best option would be to drop the subscription (possibly drop
> the publication as well to drop the triggers which will slow the delete),
> do
> the delete, and then recreate the publication and subscription and do a no
> sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
> news:eSsLlVhrFHA.3720@.TK2MSFTNGP14.phx.gbl...
> to
> process.
> is
>
|||It would remain there. This is probably not what you want, so you would have
to delete on both sides.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Ford" <tim.ford@.nospamrubbishin2focus.com> wrote in message
news:OBtaphsrFHA.240@.tk2msftngp13.phx.gbl...
> Hi Hilary,
> Thanks for that. What would happen to the information that was in the
table[vbcol=seagreen]
> on the subscriber? Would that get deleted locally, as this information
> would no longer be needed.
> Again Thanks for all you help.
> Tim.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23HJ2hOjrFHA.2592@.TK2MSFTNGP09.phx.gbl...
delete),[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
order[vbcol=seagreen]
with[vbcol=seagreen]
need[vbcol=seagreen]
are[vbcol=seagreen]
for[vbcol=seagreen]
a[vbcol=seagreen]
SQL[vbcol=seagreen]
Is[vbcol=seagreen]
that[vbcol=seagreen]
for
>

Delete rows in Excel From DTS package

I have a DTS package that needs to refresh data in 3 separate Excel
spreadsheets on a daily basis. The problem is that unless I manually
delete the previous day's data, it appends rather than replaces.

I can't delete the excel files on a daily basis, as they have to be
there for the DTS package to be able to export to Excel. What I want
to do is create a VBScript (ActiveX Control) to delete all the rows of
data except the first row within each spreadsheet as the first step of
the DTS package. Then the remaining steps would run and the
spreadsheets would only have the current day's data at the end of the
process.

Thanks for any help offered.I would recommend using a pull rather than push strategy with Excel.

Have your DTC package deposit the data in a report table and then embed
queries in the Excel spread sheet to grab that.
Your users could just hit the refresh button on their spreadsheets to get
the latest and greatest data (or write VBA script in the open even to
refresh it auto-magically).

You may be able to invoke the Excel App from the DTC script to get it to
call the refresh function as well.

We had the same issue with exporting to Excel. My crackpot theory was that
it was using the Excel ODBC driver, which appears not to be able to rewind
when streaming data into a spreadsheet. You may be able to delete the rows
to reset the spreadsheet in a separate operation from adding the new rows,
or do what we ended up doing - having DTC do a file-copy to overwrite a
template on-top of the target spreadsheet.

Happy Trails ...

<smonczka@.hotmail.com> wrote in message
news:1109285580.060720.12490@.z14g2000cwz.googlegro ups.com...
>I have a DTS package that needs to refresh data in 3 separate Excel
> spreadsheets on a daily basis. The problem is that unless I manually
> delete the previous day's data, it appends rather than replaces.
> I can't delete the excel files on a daily basis, as they have to be
> there for the DTS package to be able to export to Excel. What I want
> to do is create a VBScript (ActiveX Control) to delete all the rows of
> data except the first row within each spreadsheet as the first step of
> the DTS package. Then the remaining steps would run and the
> spreadsheets would only have the current day's data at the end of the
> process.
> Thanks for any help offered.