Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
armurthy
Starting Member
6 Posts |
 Posted - 2002-04-12 : 04:05:59
|
| hi, i am having a database where in an insert or update happens frequently thru a trigger due to which the log size increases to a great extent. can i have a solution for the log to overwrite itself or truncate the log and reduce the physical size. help pls.regardsandy |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2002-04-12 : 04:29:25
|
| schedule a maintanance plan where you backup the log, and check the option 'truncate log after backup'Check BOL for more info |
 |
|
|
armurthy
Starting Member
6 Posts |
Posted - 2002-04-15 : 02:32:13
|
| hi, thanks for the prompt reply. i already have a maintainence plan running on my server. i am using sql2000 and i dont see any option of truncate log after backup. can u pls tell me how to do it ion sq2000 and overwrite the log file.thanksandy |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-15 : 21:02:47
|
| Truncate Log on Checkpoint was a database setting in SQL 7.0. In SQL 2000, that is now controlled by the Recovery Model you choose in the Database Properties window. Simple Recovery is similar to having Truncation Log on Checkpoint checked. Be sure to read all the info on Recovery Models so you know the implications of each option.------------------------GENERAL-ly speaking... |
|
|
|
armurthy
Starting Member
6 Posts |
Posted - 2002-04-22 : 01:40:23
|
| hi, thanks for the help. i still have a problem with sql2000 log. i have the database maintainence plan. but i couldnt find the 'truncate log after backup' option. i want to allocate max of 2gb for the transaction log file. so can u tell me a way of rewriting the transaction log file. thanksandy |
|
|
|
scottpt
Posting Yak Master
186 Posts |
Posted - 2002-04-22 : 14:40:27
|
| *Create an alert to run a job to truncate or back up the log when the log gets to XX percent full.*Truncate or backup the log on a more frequent nature.*Turn truncate log on check point option on and issue a checkpoint in your trigger.One of these should provide some relief. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-22 : 21:00:50
|
| Open SQL Server Books Online (a.k.a. BOL). Click on the Index tab. Type in recover and click on the recovering databases-SQL Server, models list. Click on models and READ about the different database recovery models that are available in SQL 2000. To get similar results to having trunc. log on checkpt selected, use the Simple model. But read all of the side-effects of making this choice before you go there. Or don't. It's your job. Lose it if you wish. |
|
|
|
|
|
|
sql logs