Friday, March 16, 2012

"trunc. log on chkpt" help

First time poster...
Alright this is what I got...I am running MSSQL server 2000.
I have already restored a copy of the production db to our test sql server.

What I need to do is run some update scripts (from 3rd party vendor, going to newer version of their product). The database is about 60Gb and I filled up the transaction log (causing later updates to fail) while running one of their scripts that executed 194 routines of:

alter table Table_Name add New_Column int
GO
EXEC sp_bindefault mg_zero_dflt, [Table_Name.New_Column]
GO
update Table_Name set New_Column=1
GO

Will "sp_dboption 'dbname' 'trunc. log on chkpt' 'TRUE' " automatically truncate the transaction log when approaching the transaction log file limit while running these update scripts. Then when the updates are complete go ahead and "sp_dboption 'dbname' 'trunc. log on chkpt' 'FALSE' "

I'm not worried about logging the transactions during this process, I just don't want to have the transaction log fill up while running these scripts causing it to error out.

Or is there a way to make those changes using bcp?In the database properties window, go to the options tab and set your database recovery model to "simple". This should keep the log from filling up.
When you are finished, set the recovery model back to "full" and run an immediate backup.|||Thanks for the help. I remember seeing something about that and got sidetracked with the whole 'trunc. log on chkpt' idea. I'm in the process of finishing the scripts on the test server and I'll update with results. Thanks again!|||I finished running the scripts late yesterday without any hitches. That was exactly what I was looking for!

No comments:

Post a Comment