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 |
|
rrb
SQLTeam Poet Laureate
1479 Posts |
 Posted - 2002-11-07 : 02:26:17
|
| Hi orlhaving no knowledge of DB Admin issues, I have scanned roughly 50 posts on the transaction log, read everything I could find in BOL and still can't find an answer (I may just be dumb of course...)What I want to know is, how do I set up my new database, so that the log is truncated after each commit?The Database is created as follows:use MASTERGO...CREATE DATABASE blaON( NAME='bla', FILENAME = 'bla.mdf', SIZE = 5MB, MAXSIZE = 50MB, FILEGROWTH = 1MB )LOG ON( NAME='bla_log', FILENAME = 'bla_log.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 1MB )GO--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-11-07 : 03:51:01
|
| Hmmm...Why would you want to truncate the TL after every commit? Isn't the TL's job to save the commits for possible restoration of the DB in case of a breakdown?What is the point of truncating it so many times? |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-07 : 17:23:19
|
| Hi rihardh Good question, however this is not your normal application, and once the transaction is committed, a restoration of the data (should it breakdown) is not possible for other reasons. Without going into it in detail, in this application, if a DB fails, we scrap it and start again, rather than try to restore it back to it's previous position and continue.I guess what I'm really asking is, is there any way to turn of transaction logging - other than for a mid-transaction rollback?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-07 : 17:32:50
|
| You could change the recovery model to SIMPLE by running this:ALTER DATABASE bla SET RECOVERY SIMPLEIt won't do exactly what you are asking, but it's as close as you are going to get. See BOL to see what the simple recovery model does.Edited by - tduggan on 11/07/2002 17:33:12 |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-07 : 17:45:09
|
| thanks dugs, but you're right, this is not really what I want.From what I understand, possibly what I'm trying to achieve is this:trunc. log on chkpt +issue a checkpoint after every commit, except that I don't want to have to explicitly issue a checkpoint after every commit (for obvious reasons).Is there any setting which will allow me to do this?SQLDMOBackup_Log_NoLog looks promising - but BOL isn't really clear on how to set this other than through C++, and besides, I'm trying to avoid having to backup the log in any case.(I may be misunderstanding something so please feel free to point out the obvious)--I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 11/07/2002 17:49:20 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-07 : 18:06:19
|
| The SQL-DMO command that you reference is the same thing as BACKUP LOG bla WITH NO_LOG.Even if you set the truncate log on checkpoint flag, which I don't believe exists anymore (was a 6.5 thing), SQL Server won't necessarily perform the checkpoint when you want it to. I believe it does it every 60-90 seconds or so.The SIMPLE recovery model replaced truncate log on checkpoint option. This is at least my understanding. Maybe someone else could clarify this. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-07 : 18:12:25
|
OK I found it.thanks for your help  --I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 11/07/2002 18:15:46 |
|
|
|
|
|
|
|
|
Small transaction log