Danna,
You have a couple of options. As a permanent change (I'm assuming
you're using Microsoft SQL Server 2000) you can set your database recovery
model to "Simple" and leave it there. SQL Server 2000 has three recovery
models:
Full Complete logging. Allows for point-in-time
restore.
Bulk-Logged A lot like Full, but ignores bulk insert
operations and the like.
Simple No logging. This is the same as using the
"Truncate log on CHECKPOINT" option that was in previous versions of SQL
Server.
Using the simple recovery model means that the only kind of
database restore you can perform is from a complete backup. You no longer
have transaction logs at all, except during a transaction so it can
rollback if necessary. This is fairly risky except when you have fairly
static and easily recreated data. You should increase the frequency of
your database backups when using simple recovery.
As an alternative, you can include code in your script that will
change the recovery model on the fly when you're running your large query
and then set it back when you're done. Note that since this affects the
entire database and not just your one query, you should probably do a
database backup when you're done. The following script will display the
recovery model for the pubs database and reset it a couple of times:
SELECT DATABASEPROPERTYEX('pubs', 'RECOVERY')
GO
ALTER DATABASE pubs
SET RECOVERY BULK_LOGGED
GO
SELECT DATABASEPROPERTYEX('pubs', 'RECOVERY')
GO
ALTER DATABASE pubs
SET RECOVERY FULL
GO
SELECT DATABASEPROPERTYEX('pubs', 'RECOVERY')
GO
Third, you can rewrite your query to use bulk insert functions.
However, you originally said that you were performing updates, which
means this is probably not an option. However, BOL has all the details in
case you're interested in doing bulk copies.
Thanks,
Eric
"Danna D. Swain" <[EMAIL PROTECTED]>
09/11/2003 01:36 PM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: transaction log
Is there any way that you can stop the transaction log from logging
updates?
I have to run a pretty big update every hour and it is filling up the
transaction log too quickly. Any ideas??
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:6
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
