The recovery model is set to simple and the log still fills up during the update??
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, September 11, 2003 4:01 PM To: SQL Subject: Re: transaction log 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
